SOLVED

Help with double cell lookup formula

Copper Contributor

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:

techyaccountant_1-1715097351763.png

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. 

 

 

3 Replies

@techyaccountant 

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?
best response confirmed by techyaccountant (Copper Contributor)
Solution

@techyaccountant 

=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.

1 best response

Accepted Solutions
best response confirmed by techyaccountant (Copper Contributor)
Solution

@techyaccountant 

=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.

View solution in original post