Lookup in VBA Table Without LOOPS - Excel VBA Is Fun

Russell asked if one could go through a table and instead of looping through to find a match and offsetting that cell, can he do it another way? YES! Check it out.

Fantastic Developer Tools:

[krn]

Great job Dan. I like to use Index and Match together also.

[blgrth]

This is okay if each name is column name is unique. How could I adjust this if there were two identical names?

[sylviabaharet]

Thank you for your reply. I appreciate the help. The issue with the above (and please correct me if I am wrong), is that the two conditions are not preexistent, they are generated by calculations embedded in loops and only then the vlookup will have the 2 conditions to be used. Also the range (2 columns) generated by the 2 conditions is not always the same length. Thank you.

[ExcelVbaIsFun]

Try something like this: =INDEX(Sheet1!X1:X15000, MATCH(1, (Sheet1!B1:B15000="Bazooka")*(Sheet1!C1:C15000="OtherSearchItem"), 0))

Don't hit enter, this is an array function, so hold ctrl + shift + enter. It will put squiggly brackets around the function and return what you need. You may replace the "Bazooka" and other part with what you need, including a cell nearby the function result. Follow up? Ask away. Also google "vlookup multiple criteria" to find more on the INDEX and MATCH array codes.

[sylviabaharet]

Hi, I was wondering if this can be done with two conditions (finding a result in column x that meets 2 conditions) as opposed to one condition as in this example but also without looping. I ask because I have this issue but the table I need to use for the 2 conditional lookup is 12, 000 lines long. I just need to drop the result of the lookup into an empty column in the same line as where the 2 conditions are. I would really appreciate the help as I am really stuck. Thanks!