Multi Column Vlookup

Let’s look at how to use VLOOKUP to get results from the same Lookup Value in multiple columns.

Let’s use the example we used in vlookup to see if we can get more corresponding values from a table with the same lookup value. Let us also obtain “Phone Number” and “Country” using “Id” as lookup value.

When we try to auto-fill the columns, we get this:

The reason for this is that when we try to autofill columns, the lookup value, range of the table, i.e. table array, will change automatically, whereas col_number will remain constant. So, how do we solve this?

The cell with the lookup value and table array must be locked in the formula. We use ” $ ” sign before the cell to lock the cells. Only the cells whose table array and lookup value should not change when the columns are autofilled should be locked. So our new formula looks like this:

=VLOOKUP($C$19,$B$3:$F$11,2,0)

When we use this formula and try to autofill we get this:

We can see that the lookup value, table range, and col_number are all the same now, that is why we got the same result. To get the correct results, we must modify col_number for each cell manually, however this is a time-consuming and tedious process that we must avoid. To do that we use match function.

Let’s have a look at what the match function is and how it may help us autofill columns in the VLOOKUP function.

MATCH FUNCTION

The MATCH function in Excel is used to find the position of a lookup value in a row, column, or table. The main purpose is to get the position of an item in an array. It returns us the number of the position of a value in an array. For example,

=MATCH (lookup_value, lookup_array, [match_type])

For detailed explanation check out our match function blog.

Look at this example:

This indicates that “ID” is at position 1 in the array B3:F3, whilst Name, Phone Number, and Country are at positions 2,4,3, respectively. We no longer need to manually enter the column number in the vlookup function.

Now instead of entering col number manually in the vlookup function, let’s use the match function to reduce manual work and get the correct result while trying to autofill columns. So, our new formula looks like this:

=VLOOKUP($C$19,$B$3:$F$11,MATCH(D18,B3:F3,0),0)

When we use this formula and try to autofill we get this:

This is because when we try to autofill columns, the cell range inside the match function changes automatically. To solve this issue, we must use the ” $ ” symbol to lock the cell range. So, our new formula looks like:

=VLOOKUP($C$19,$B$3:$F$11,MATCH(D18,$B$3:$F$3,0),0)

When we use this formula and autofill we get the final result:

Practice Multi Column VLOOKUP Below:

The formula can be entered in the highlighted cell. If the formula entered is correct, it shows good job. If the formula is entered incorrectly, the result will be invalid. Please keep in mind that you can only enter formula in the highlighted cells.

Hurray! You know now how to use multi column vlookup function. Let us get into more depth of vlookup function, learn how to use multi row vlookup, multi row and column vlookup which is most important and useful fucntion in excel.