Vlookup using 2 lookup tables (use of If function)

Subscribe my youtube channel!

 

Today’s exercise implies an increase of difficulty level, the use of Vlookup using two possible lookup tables.

Image below show us 3 tables. Two of them give the prices of rooms for 3 different hotels (for 1-2 persons), where each table gives us low season  and high season prices:

The function of the third table (the blue table) is to return the price per room depending the number of person, season and hotel name.

To find the price, i have to lookup in the two main tables the input info that im going to put in the blue table (number of people, season and hotel name) using two function (If and Vlookup)

The If function will decide which of the two tables i use (depending if its low or high season). Vlookup will find the hotel that i want (hotel name) and when find the name,  return value for 1 or 2 persons (depending the value that i put in number of people cell).

Example of price for Hotel Madrid (Cell C11), in low season (Cell B11) for 2 persons (Cell A11):

Starting with If function sintax:

This function has 3 important arguments:

• Logical test that receives value or expression and return TRUE (1) or FALSE(0)
• Value_if_true returns a value if Logical_test is true
• Value_if_false returns a value if Logical_test is false

If i want to decide what table to use (low or high season), my logical_test will be the text that i input in cell B11 (season):

ISNUMBER(SEARCH(B11;A1)) -> Search if text in B11 appears in Text A1 (text “low season”) and returns the number position where text B11 is found in text A1.

If the text is not find will return #VALUE!

Example of function SEARCH:

SEARCH(“abc”;”xpabc”) =3 (first letter of text “abc” is found in position 3 of text “xpabc”)

SEARCH(“abc”;”xp”) =#VALUE!

You know that the logical_text must return TRUE or FALSE, so i add function ISNUMBER that receives the result of SEARCH function ( this function returns a number or #VALUE!).

The function ISNUMBER returns TRUE (1) if the parameter is a number or returns FALSE(0) if is not a number (#VALUE!). The first parameter of IF function is now explained and completed.

Second and third parameter of IF function is the value if logical_test is TRUE or FALSE.

If the condition  ISNUMBER(SEARCH(B11;A1)) is TRUE it means that in cell B11  appears in cell A1 text(“Low season”), and we are talking about table low season. If its FALSE its the high season table.

In the case below i put “high” in cell B11, so when using ISNUMBER(SEARCH(B11;A1)), text “high” does not appear in A1 text (Low season), that’s why return FALSE:

So after choose the table (low or high), i will use the Vlookup to choose the price in function of the hotel name (C11) and number of person(A11).

If my logical_test is TRUE (low season), my Vlookup will be (Value_if_true):

VLOOKUP(C11;B2:D5;MATCH(A11;B2:D2;0);FALSE), where:

• C11 is the lookup_value (hotel name)
• B2:D5 is the table array
• MATCH(A11;B2:D2;0) will return column C or D depending the A11 value (1 or 2)
• False to return exact_value

In case of high season table (Value_if_false):

VLOOKUP(C11;I2:K5;MATCH(A11;I2:K2;0);FALSE), where:

• C11 is the lookup_value (hotel name)
• I2:K5 is the table array
• MATCH(A11;I2:K2;0) will return column J or K depending the A11 value(1 or 2)
• False to return exact_value

The final formula is:

IF(ISNUMBER(SEARCH(B11;A1));VLOOKUP(C11;B2:D5;MATCH(A11;B2:D2;0);FALSE);VLOOKUP(C11;I2:K5;MATCH(A11;I2:K2;0);FALSE))

For the case where room is for 2 persons in high season for hotel madrid

You can  download here this file: vlookup_if

• Excel
• If
• Match
• Vlookup