A two-way lookup pulls a value from the intersection of a row and a column — say the sales figure for September in the North zone. The classic method makes VLOOKUP’s column number dynamic with MATCH: =VLOOKUP(G1,A1:E13,MATCH(G2,A1:E1,0),0), where G1 holds the month and G2 the zone.
MATCH finds which column the zone sits in, and VLOOKUP returns the value from that column. INDEX with two MATCH functions is the more flexible version, since it doesn’t care which side the lookup columns sit on: =INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0)) — the first MATCH gives the row, the second the column.
In Excel 365 or 2021, nested XLOOKUP is the cleanest: =XLOOKUP(G1,A2:A13,XLOOKUP(G2,B1:E1,B2:E13)). The inner one picks the column, the outer one picks the row. All three need the 0 for exact match, and all three return #N/A if the row or column label doesn’t match exactly — a trailing space in either heading is the usual cause.
Sometimes, we need to look up a value from a table according to the row heading and the column heading.
Let’s say you need to get the values from the above table for a particular month and zone. Here in this situation, you can use a two-way lookup.
Performing a two-way lookup is all about getting a value from a two-dimensional table. That means you can get a value from any cell of a table.
And for this, you need to combine the match function with VLOOKUP.
As you know, a normal VLOOKUP is only able to return a value by looking into a single column at a time.
The reason is that the col_index argument of VLOOKUP is a static value. But if you combine the MATCH function with VLOOKUP, you can make it dynamic.
How to Use VLOOKUP + MATCH for a Two-Way LOOKUP
Let’s come back to our example. We have a table with monthly sales quantity for four different zones of a company.
Now let’s say, your boss wants to check the quantity for a zone for a particular month (For Ex: the month Sep and zone North). And he doesn’t want to search the table for it. Here is the formula:
=VLOOKUP(month,table,MATCH(zone,heading_row,0),0)
How it Works
As I already said, we need the MATCH function to make col_index dynamic. This formula works in two parts. When you select the zone, the MATCH function returns the position of the zone in the heading row.
And then VLOOKUP uses that position to get the value from that column.
Every time you select a month and a zone, it will return the value at the intersection of that row and column. The thing is, you have a dynamic col_index, which allows you to get values from different columns with VLOOKUP.
There’s one thing to take care of: the heading row in MATCH must start from the same column as the table. If the table is A1:D3, use A1:D1 as the heading row, not B1:D1. Otherwise, MATCH returns a position that is one column short, and VLOOKUP returns the value from the wrong column.
=VLOOKUP(F1,A1:D3,MATCH(G1,A1:D1,0),0)
In this formula, the month is in F1, the zone is in G1, and the table is A1:D3 with the months in column A and the zones in row 1.
Two-Way Lookup with INDEX and MATCH
The other popular way is INDEX with two MATCH functions: one MATCH finds the row of the month and the other finds the column of the zone. It doesn’t need the lookup column to be the first column of the table, so it works in more situations than VLOOKUP.
=INDEX(B2:D3,MATCH(F1,A2:A3,0),MATCH(G1,B1:D1,0))
Here, the values range (B2:D3), the month list (A2:A3) and the zone headings (B1:D1) don’t include the headers, so all the positions line up.
Two-Way Lookup with XLOOKUP
If you use Excel for Microsoft 365, Excel 2021 or later, you can nest one XLOOKUP inside another. The inner XLOOKUP returns the whole row of the month, and the outer one picks the zone from that row:
=XLOOKUP(G1,B1:D1,XLOOKUP(F1,A2:A3,B2:D3))
Handle Values That Are Not Found
If the month or the zone isn’t in the table, the formula returns #N/A. Wrap it in IFERROR to show a message instead. Before that, check for extra spaces or different spellings in the headings, as they are the most common reason for #N/A.
=IFERROR(VLOOKUP(F1,A1:D3,MATCH(G1,A1:D1,0),0),"Not found")
Frequently Asked Questions
Why does my two-way lookup return the wrong column?
The heading row in MATCH doesn’t start in the same column as the VLOOKUP table. Make both start from the first column of the table.
Can I do a two-way lookup with numbers only?
Yes. When the values are numbers, you can also use SUMIFS on the column that INDEX and MATCH return. It returns 0 instead of #N/A when the month isn’t found:
=SUMIFS(INDEX(B2:D3,0,MATCH(G1,B1:D1,0)),A2:A3,F1)
Is the two-way lookup case-sensitive?
No. VLOOKUP, MATCH and XLOOKUP treat “sep” and “Sep” as the same value.
lot of things to learn in excel
thanks for your sincere efforts
I need a solution. Please help.
I have list of priorities. P1 P2 P3 P4.
Resolution time is
P1 – 2 hours
P2 – 4 hours
P3 – 1 Business day
P4 – 5 Business day
P4 Priority
Start time is
22-03-2022 10:50:00
Resolved time is
24-03-2022 11:30:00
Help me calculate SLA met or not met.
why not use unpivot in power query?
Hi Puneet, awesome but prefer index and match
I got your point.
But I found this method simple.
I agree that INDEX-MATCH has multi use.
This combination is great and will be extremely useful. Thank you for explaining and sharing all your guides.