Hello,
I am using a VLOOKUP formula incorporating the INDIRECT function and want to replace the indirect function with something else, perhaps a match, index, choose, offset or some other function. I cannot figure out a way to write this formula with a different function and need help. Here is a link to my file.
The following formula in cell B7 is:
=VLOOKUP(C4,INDIRECT(N7),2)
Cell C4 is a value greater than 1.
The formula in cell N7 is:
=VLOOKUP(NUM,DATA,7)
NUM is cell C2 and can be a value greater than 1. The contents of range name DATA is
1 Asample D E F G AAA
2 Bsample D E F G BBB
This table can have up to 100 rows and 50 different values in column 7.
The formula in N7 therefore returns AAA or BBB by matching the value of NUM in range DATA and looking up the contents of column 7. AAA or BBB are range names of other tables which are used in cell B7 thanks to the INDIRECT function. Using this function B7 does a lookup in table AAA or BBB.
Table AAA looks like this
0 5.75%
100,000 4.75%
250,000 3.75%
500,000 2.00%
1,000,000 0.00%
Cell B7 will return a value equal to =LOOKUP(C4,BBB,2). If cell C4 is equal to 125,000 then the value returned in B7 is 4.75%. I cannot hardcode the range name of the table because the user can change the value of NUM.
Any suggestions?
Thanks for your help!!
Bookmarks