How to do a 2D Lookup in Excel? Let’s start.

2D Lookup | Excel Tips 68

When we want to find an inter­sec­tion between a row and a col­umn bas­ing on head­ers, we can do it in two dif­fer­ent ways. The first one will be in Lega­cy Excel, and the sec­ond in Dynam­ic Array Excel.

The first solu­tion uses the MATCH func­tion as well as its close friend, the INDEX function. 

Let’s start with look­ing for the coun­try. In this case we want to find the row num­ber. Let’s write the cell, the range where we will be look­ing for and 0 for an exact match (Fig. 1)

=MATCH(B8,A3:A6,0)

MATCH function for countries
Fig. 1 MATCH func­tion for countries

We also want to find the col­umn num­ber. In this case we also use the MATCH func­tion. Let’s the cell, the range where we will be look­ing for and 0 for an exact match (Fig. 2)

=MATCH(B9,B2:G2,0)

MATCH function for months
Fig. 2 MATCH func­tion for months

When we have our row and col­umn num­bers, we can use the INDEX func­tion, select the whole dataset, then write the row and col­umn num­bers (Fig. 3)

=INDEX(B3:G6,D8,D9)

INDEX function
Fig. 3 INDEX function

Just like that, we did the 2D lookup, or we have found the val­ues we want­ed (Fig. 4)

Results
Fig. 4 Results

So far, we have had three for­mu­las. If we want to have only one, we need to imple­ment some mod­i­fi­ca­tions. We have to copy our MATCH func­tion and paste it in the place refer­ring to cell D8. Then, we have to copy the sec­ond MATCH func­tion with­out the equal sign, and paste it in the place of the ref­er­ence to cell D9. Now, we have one, big­ger for­mu­la instead of three small­er ones (Fig. 5)

=INDEX(B3:G6,MATCH(B8,A3:A6,0),MATCH(B9,B2:G2,0))

One, big formula
Fig. 5 One, big formula

Now, let’s focus on the solu­tion in the Array Excel. We will be using here the XLOOKUP func­tion twice. 

First, we are look­ing for a row in the coun­try head­ers. Then, as a return­ing array, we give Excel the whole dataset (Fig. 6)

=XLOOKUP(B8,A3:A6,B3:G6)

 XLOOKUP function
Fig. 6 XLOOKUP function

Since I’m using the XLOOKUP func­tion, and we are work­ing in the Array Excel, Excel will return the whole row. 

In such a case, we have to find a prop­er col­umn. We can also do it with the XLOOKUP func­tion. Let’s write the XLOOKUP func­tion once again. This time we will be look­ing for a month in the month head­ers. As a return array, we have the row we found pre­vi­ous­ly (Fig. 7)

=XLOOKUP(B9,B2:G2,XLOOKUP(B8,A3:A6,B3:G6))

Another XLOOKUP function
Fig. 7 Anoth­er XLOOKUP function 

And just like that, we have our results (Fig. 8)

Results
Fig. 8 Results

Even if we change the val­ues in our for­mu­la, the result is still cor­rect (Fig. 9)

Checking the formula
Fig. 9 Check­ing the formula

The results are cor­rect both in XLOOKUPs and INDEX with MATCH functions. 

https://www.youtube.com/watch?v=fZqiVB3Vffw