How to lookup across two tables by a condition is a combination of the VLOOKUP and the IF functions. The IF function handles which table and VLOOKUP handles what to search for.
Syntax
=VLOOKUP(lookup value, IF(condition, first table if condition is TRUE, second table if condition is FALSE), column number, 0)
Steps
- Start with =VLOOKUP(
- Select or type the cell reference that includes lookup value E6,
- Use IF( function to choose between tables
- Enter your condition $E$3="Pikachu",
- Select the table that is used when condition is met $B$3:$C$6
- Select the table that is used when condition is NOT met $B$9:$C$12
- Type ) to close the IF function
- Type the column number where value is searched 2
- Use 0) to ensure exact search and finish the formula
How
The IF function is used to identify the data range. Although returning a range may seem meaningless by using the formula itself, it works when it is used with another formula that accepts a range as a parameter.
=IF($E$3="Pikachu",$B$3:$C$6,$B$9:$C$12) returns #VALUE! error.
The VLOOKUP searches for the value in its first parameter in the first column of the range that is specified in the second parameter. If there is an exact or approximate match, it then returns the value in nth column in range. The n is the third parameter. Finally, the last parameter determines if it is an exact match or an approximate match.
The key parameter is the second one, the table range. If we replace the static range with a range returning IF function, we will have a VLOOKUP formula with a dynamic range that can lookup across two tables.
=VLOOKUP(E6,IF($E$3="Pikachu",$B$3:$C$6,$B$9:$C$12),2,0
