Forum Discussion
Help with double cell lookup formula
I am trying to pull a value based on one worksheet data into another, essentially being a vlookup based on ID but also with the condition it matches an item name.
Example:
I want to find the value of C3 and D3 from column H and I by looking at both A3 and B3. I cannot seem to figure out the extra condition/formula to use with a traditional vlookup since I am doing it based on two conditions.
=VLOOKUP($B3&$A3,CHOOSE({1,2},$F$3:$F$10&$G$3:$G$10,H$3:H$10),2,FALSE)
The CHOOSE function allows to create a lookup range (red) and a return range (purple).
In the attached file i've added the alternative INDEX and MATCH formula.
The solution in the attached file is with VLOOKUP. Alternatively you can use INDEX and MATCH. The formula is in cell C3 and filled across range C3:D10. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
- Thank you OliverScheurich! What exactly is the Choose formula doing, just for my better understanding?
=VLOOKUP($B3&$A3,CHOOSE({1,2},$F$3:$F$10&$G$3:$G$10,H$3:H$10),2,FALSE)
The CHOOSE function allows to create a lookup range (red) and a return range (purple).
In the attached file i've added the alternative INDEX and MATCH formula.
