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.
- OliverScheurichGold Contributor
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.
- techyaccountantCopper ContributorThank you OliverScheurich! What exactly is the Choose formula doing, just for my better understanding?
- OliverScheurichGold Contributor
=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.