V
VDU
Hello,
I have a problem trying to make a formula that can search for a value in an
array based on 3 starting values contained in 3 separate cells.
Each of the 3 starting cells contains :
1. sheet name
2. value to search in a row
3. value to search in a column.
Setting the sheet is the hardest part.
the main function is not a problem, an index with 2 match functions for
row_num and col_num but defining the arrays in which the functions search
seems difficult as the sheet name in the array has to be taken from a cell.
I also tried an Indirect (match(Adress)) solution but that doesn't worork
either, here is the formula I came up with:
INDIRECT("'"&B$2&"'!R"&(10+Match(B5, address(11, 2, 1, true,
B2):Address(65000, 2, 1, true), 0))&"C"&(2+Match(A5, address(2, 3, 1, true,
B2):Address(2, 200, 1, true, B2), 0)), false), FALSE)
If you can tell me what to change for any of the 2 solutions or have a third
one please help. Important to mention is that I used to use a macro for this
operation but I can't do that any more, I have to use a function.
Thank you,
I have a problem trying to make a formula that can search for a value in an
array based on 3 starting values contained in 3 separate cells.
Each of the 3 starting cells contains :
1. sheet name
2. value to search in a row
3. value to search in a column.
Setting the sheet is the hardest part.
the main function is not a problem, an index with 2 match functions for
row_num and col_num but defining the arrays in which the functions search
seems difficult as the sheet name in the array has to be taken from a cell.
I also tried an Indirect (match(Adress)) solution but that doesn't worork
either, here is the formula I came up with:
INDIRECT("'"&B$2&"'!R"&(10+Match(B5, address(11, 2, 1, true,
B2):Address(65000, 2, 1, true), 0))&"C"&(2+Match(A5, address(2, 3, 1, true,
B2):Address(2, 200, 1, true, B2), 0)), false), FALSE)
If you can tell me what to change for any of the 2 solutions or have a third
one please help. Important to mention is that I used to use a macro for this
operation but I can't do that any more, I have to use a function.
Thank you,