How to use vlookup function where lookup table array is dynamic? I find the column where I need to lookup values using MATCH function. Can you please suggest how to put integer value coming from MATCH to lookup array function.
Asked
Active
Viewed 822 times
0
-
1You can use Indirect Function for that. Construct the Address using Variables in InDirect Function. – Mikku Jul 12 '19 at 04:35
1 Answers
2
I'm not sure if this is all you're asking, but if you're trying to match the column, you can just include the entire range of the spreadsheet. Or you could use an Index function to resize. Lots of options.
To directly answer your question, here's a dynamic vlookup that will include any possible row (as it extends all the way to the bottom of the spreadsheet), and uses Match, to lookup the column header DONKEY. Not very elegant, but it'll get you started. In this case the formula would return 49.
Here's the formula if you want it: =VLOOKUP("California",$I$5:$XFD$1048576,MATCH("Donkey",i4:$XFD$4,0),0)
UPDATE somehow the above answer has gotten two likes so I will make a bigger effort to enhance this answer by explaining that Index Function would probably be a better approach than Vlookup. The formula would look like this: =INDEX(J5:M7,MATCH("Cobra Kia",I5:I7,0),MATCH("Donkey",J4:M4,0),1)
Also, OP tagged VBA although it doesn't seem like this is needed. However if using VBA and trying to do MATCH, always us Application.Match approach, even though it doesn't auto populate like Application.WorksheetFunction.Match. I wrote about that too in this answer... (still not as good as my SHA256!)
pgSystemTester
- 8,979
- 2
- 23
- 49
-
Sorry first screen shot was wrong. I started giving an index answer. – pgSystemTester Jul 12 '19 at 04:50
-
1
-
@JvdV done! I don't know how this answer has more likes than this answer I spent literally almost all day on.... https://stackoverflow.com/questions/46509572/excel-formula-based-function-for-sha256-sha512-hashing-without-vba-or-macros/56767828#56767828 – pgSystemTester Jul 12 '19 at 08:32