SOLVED

# Help with double cell lookup formula

Copper Contributor

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

3 Replies

# Re: Help with double cell lookup 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.

# Re: Help with double cell lookup formula

Thank you @OliverScheurich! What exactly is the Choose formula doing, just for my better understanding?
best response confirmed by techyaccountant (Copper Contributor)
Solution

# Re: Help with double cell lookup formula

=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

# Re: Help with double cell lookup formula

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