Excel Function -Address

[joshua1990]

Hey guys
!
Although it is not a VBA code, but maybe can help me Yes nonetheless.

I want to perform the following function:
=ROUND(LOOKUP(RAND(); ADDRESS (22) (COLUMN ((INDEX(tblData!2:2;))) MATCH(B1;tblData!2:2;0))):address(50;(column((index(tblData!2:2; MATCH(B1;tblData!2:2;0)))+1));1)

Unfortunately, I get the error that the function contains an error. I think this is the area ADDRESS: ADDRESS.
The formula are in the original condition as:
=ROUND(LOOKUP(RAND(); initalcell: end cell; 1).

The initial cell is determined as follows:
ADDRESS (22) (COLUMN ((INDEX(tblData!2:2;))) MATCH (B1;tblData!2:2;0)))

The end cell according to the same logic.


How do I clean the addresses of both cells now in my function?

[mdmackillop]

Can you post a sample workbook?

[joshua1990]

Oh, damn, I did not manage to upload the file. I'm also back in the office on next monday.

I hope, however, to get on with the following description.

The function has the following structure: = ROUND (LOOKUP (RAND (); tblData! C22: tblData! D39); 1)

The initial line should be defined according to the following formula:
"! tblData" = ADDRESS (22; (COLUMN ((INDEX (tblData 2: 2; MATCH (B1; tblData 2:! 2; 0))))))

The end line according to the following logic:
"! tblData" = ADDRESS (50; (COLUMN ((INDEX (tblData 2: 2; MATCH (B1; tblData 2: 2; 0))))) + 1)

The sum is thus as follows:
=ROUND(LOOKUP(RAND();"tblData!"&ADDRESS(22;(COLUMN((INDEX(tblData!2:2;MATCH(B1;tblData!2:2;0)))))):"tblData!"&ADDRESS(50;(COLUMN((INDEX(tblData!2:2;MATCH(B1;tblData!2:2;0)))))+1);1)

Unfortunately Excel recognizes an error, but does not say exactly where.
It is probably due to the addressing of the two cells. Where is the mistake?

[Bob Phillips]

Try this formula


=ROUND(LOOKUP(RAND();OFFSET($A$1;21;MATCH(B1;tblData!2:2;0)-1;50-22+1;2));1)