This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

How to use VLOOKUP with columns and rows

VLOOKUP with rows and columns

VLOOKUP with rows and columns

There are times when our data is laid out in columns and rows.  In these circumstances, we may need to look up to both the column and the row at the same time, such as in the data below.

VLOOKUP with columns and rows

But how can we achieve this?

 

VLOOKUP & MATCH

By combining the VLOOKUP function with the MATCH function, we can achieve a lookup to a row and a column at the same time; this is often referred to as a two-way lookup.

MATCH

The MATCH function is a very useful; it returns the position of a lookup value within a range.

Using our example data; we can find the column number of “Jun” using the Match function.

VLOOKUP rows and columns MATCH

=MATCH("Jun",B1:M1,0)

Advertisement:

The result of this formula is 6, as in the Range B1-M1 “Jun” is the 6th item.  If we were to look up “Nov”, this would return 11, as that is the 11th item.

The last argument in the MATCH function is important.  We will be using 0 as that will provide an exact match.

 

VLOOKUP & MATCH together

We can insert MATCH into the VLOOKUP function in place of the column number.

The VLOOKUP function counts the first column as 1, but our MATCH function starts at column B, so it is necessary to add 1 to the column number for the VLOOKUP to return the value from the correct column.

VLOOKUP combined with MATCH

The formula in B12 is as follows:

=VLOOKUP(B9,A2:M5,MATCH(B10,B1:M1,0)+1,FALSE)

 

Looking up multiple rows

We’ve seen, in previous posts, that it is possible to use VLOOKUP with multiple criteria where the data is in two or more columns.  But what if we want to match multiple rows?


Advertisement:

The example below shows July appearing twice in our data, once for 2016 and once for 2017.  By making the MATCH formula an array formula we can match the two column criteria of month and year together.

VLOOKUP combined MATCH (array)

The formula in cell B14 is:

{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)}

This formula is starting to look a bit complicated now, so let’s break it down.

Firstly, this is an array formula.  Type the formula into Excel without the { }, but press Ctrl+Alt+Enter to enter the formula.  Excel will then add the { } by itself automatically.

{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)}

Secondly, let’s just look at the first argument of the MATCH function.  This is simply combining the values of “Jul” and “2016” together with a spacer character in the middle.

{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)}

The next argument of the MATCH function creates a temporary array of values with a spacer character in the middle.  This only works because it is an array formula.

{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)}

The temporary array would include the following {“Jul^2016” , “Aug^2016” , “Sep^2016” , “Oct^2016” […all the way up to…] “Jun^2017” , “Jul^2017”}


Advertisement:

The lookup value in the MATCH function is compared to this temporary array.  Provided the year and the month match a value will be returned.  By changing the year in cell B12 the value from N5, rather than B5 will be returned.  The image below shows the result as 27, rather than 23.

VLOOKUP combined MATCH (array) 2

 

Multiple condition rows and columns

If you ever need to match multiple condition rows and multiple condition columns together, then it’s probably best to consider the INDEX/MATCH/MATCH formula.  As I’m not sure it’s possible to push the VLOOKUP formula that far.

 

Download the Advanced VLOOKUP Cheat Sheet

The Advanced VLOOKUP Cheat Sheet includes most of the tips and tricks covered in the Mastering VLOOKUP Series. Download it and pin it up at work; you can even forward it onto your friends and co-workers.

Advanced VLOOKUP Cheat Sheet

Click here to download the Cheat Sheet

 

Other posts in the Mastering VLOOKUP Series

Save