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 “xp**a**bc”)

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

Can you pls attach a file of your example.

LikeLike

Hello Anand. I put the file online at the end of the post.

Thanks

LikeLike

Got It. Thanks ☺………

LikeLike

=VLOOKUP(C11,IF(ISNUMBER(SEARCH(B11,A1)),B2:D5,I2:K5),A11+1,FALSE)

This simply does the job. And it is shorter.

LikeLike

Hello udayaspeaks. Thanks fot your feedback. There are many ways to extract the price value. Your point of view is very interesting. You switch the positions of IF and Vlookup. When i made this exercise i didnt think in that way. Thanks for your contribution.

Congrats,

Luis

LikeLiked by 1 person