Advanced Excel VLOOKUP Formula with Match

Assume that you a have price card for different bike models, and the prices of Bike model is different in various cities like Mumbai, Pune, Bangalore

So here there are 2 variables.

1. Bike Models from top to bottom

2. Name of the cities

Now you want to find out the price of Bike ZX 200 model in Mumbai city from the given table

So to find out the price, we will be applying the 2d VLOOKUP Formula by following the steps given below:

1. Press =VLOOKUP

Now there are 4 parameters (lookup_value, table_array, col_index_num, [range_lookup])

The first parameter lookup_value means the value to look for in the first column of a table.

2. Choose Bike ZX 200 cell which is given vertically in the table =VLOOKUP(B8

The second parameter table_array means the table from which to retrieve a value.

3. Select the entire table and press F4 =VLOOKUP(B8,$B$11:$E$14,

The third parameter col_index_num means the column in the table from which to retrieve a value.

4. Write 3 =VLOOKUP(B8,$B$11:$E$14,3

Note – We want to find out the price in Mumbai city which lies in 3rd column

The fourth parameter range_lookup means TRUE = approximate match and FALSE = exact match.

5. Write FALSE or 0 to get the exact match of an answer =VLOOKUP(B8,$B$11:$E$14,3,

6. Close the bracket and press Enter =VLOOKUP(B8,$B$11:$E$14,3,)

7. You will get your answer 84150

Now the problem lies when you change the City from Mumbai to Pune to know the price of Bike ZX200 model then the price will not get changed because you will get the prices as per column number

So inside the VLOOKUP formula, we will remove the col_index_num which was applied before and instead, we will apply MATCH Formula

Note – MATCH Formula will help us to find out the answer from Mumbai, Pune and Bangalore city which is in 2, 3 and 4 column in the given table

1. Press =MATCH

Now there are 3 parameters (lookup_value, lookup_array, [match_type])

The first parameter lookup_value means the value to match in lookup_array.

2. Select the Pune cell =MATCH(C8

The second parameter lookup_array means range of cells

3. Select the headers from the given table and press F4 =MATCH(C8, $B$11:$E$11,

The third parameter match_type means 1 =less than 0 = exact match, -1 = greater than

4. Write 0 as exact match and close the bracket =MATCH(C8, $B$11:$E$11,0)

5. Press Enter and you will get your answer as 82500

6. Lastly, if you change your city from Pune to Mumbai or Bangalore, you will answer different prices of Bike Models from the given table.