VLOOKUP Two-Way Lookup: How to Use It

A two-way lookup in Excel (or Google Sheets) uses VLOOKUP (or a combination of functions) to find a value at the intersection of a specific row and a specific column. You enter the row lookup value and the column lookup value, and the formula returns the matching cell’s content. This article explains the standard method, a more robust alternative, and common pitfalls.

Formula

The classic two-way lookup formula uses VLOOKUP with a MATCH function for the column index. The general form is:

=VLOOKUP(lookup_value, table_array, MATCH(column_header, header_row, 0), FALSE)

• lookup_value – the value you search for in the first column of the table.
• table_array – the entire data range, including the header row and the first column.
• column_header – the value you want to match in the header row (e.g., a month name or a category).
• header_row – the range that contains the column headers.
• 0 in MATCH ensures an exact match.
• FALSE in VLOOKUP forces an exact match for the row lookup.

This formula works when the lookup column is the leftmost column of the table. If it isn’t, you need INDEX/MATCH or XLOOKUP (see below).

Worked Example

Suppose you have a sales table:

• Column A: Product names (A2:A10)
• Row 1: Month names (B1:M1)
• Data: sales figures for each product and month

You want the sales of “Widget” in “Mar”. The formula is:

=VLOOKUP("Widget", A1:M10, MATCH("Mar", A1:M1, 0), FALSE)

MATCH(“Mar”, A1:M1, 0) returns 4 (because Mar is the 4th column, counting from B). VLOOKUP then finds “Widget” in column A and returns the 4th column value. If the table has 3 products and 3 months, and the data is arranged so that the intersection of row 2 and column 3 is 150, the formula returns 150.

When to Use a Two-Way Lookup

Use it when your data is organized as a grid (matrix) and you need to retrieve a value based on two criteria. Common examples:

• Sales figures by product and month
• Employee salaries by name and department
• Test scores by student and subject
• Pricing tables by item and quantity

It’s essentially a lookup on both the row and column headers.

How to Choose Between VLOOKUP, INDEX/MATCH, and XLOOKUP

VLOOKUP with MATCH is the traditional method and works in all Excel versions. However, it has limitations:

• The lookup column must be the first column of the table.
• It can’t look to the left (return values from columns before the lookup column).
• Adding or removing columns can break the column index.

INDEX/MATCH is more flexible: =INDEX(data_range, MATCH(row_lookup, row_range, 0), MATCH(col_lookup, col_range, 0)). It allows any column order and is generally faster.

XLOOKUP (Excel 365, 2021, and Google Sheets) simplifies everything: =XLOOKUP(row_lookup, row_range, XLOOKUP(col_lookup, col_range, data_range)). It’s the recommended modern approach.

Common Mistakes and How to Fix Them

• Mismatched ranges: The MATCH range must be the same height as the VLOOKUP table’s header row. If they differ, you get #N/A.
• Exact vs. approximate match: Always use FALSE (or 0) for exact match unless you specifically need approximate (e.g., tax brackets).
• Text vs. numbers: If your lookup value is a number but the column contains text (or vice versa), it won’t match. Ensure consistent formatting.
• Hidden spaces: Extra spaces in headers or lookup values cause mismatches. Use TRIM() to clean.
• Column index changes: If you insert a column inside the table, the MATCH result shifts. Always use MATCH, not a hardcoded number.

Alternatives to VLOOKUP for Two-Way Lookups

Besides INDEX/MATCH and XLOOKUP, you can use:

• SUMPRODUCT for numeric data: =SUMPRODUCT((row_range=row_val)*(col_range=col_val)*data_range). This works but only for numbers and is less readable.
• GETPIVOTDATA if your data is a PivotTable.
• Power Query to reshape data into a flat table, then use simple lookups.

Frequently Asked Questions

Can I do a two-way lookup without VLOOKUP?

Yes. INDEX/MATCH is a popular alternative, and XLOOKUP is the modern choice. Both are more flexible and don’t require the lookup column to be first.

Why does my VLOOKUP return #N/A?

Usually because the lookup value isn’t found exactly, or the column index is out of range. Check for typos, extra spaces, and that the MATCH range is correct.

How do I do a two-way lookup in Google Sheets?

Google Sheets supports VLOOKUP, INDEX/MATCH, and XLOOKUP (as of 2022). The syntax is the same as Excel. You can also use QUERY for more complex lookups.

Can I use wildcards in a two-way lookup?

Yes, in VLOOKUP and MATCH you can use * and ? if the lookup value is text and you set match type to 0. For example, MATCH(“Mar*”, header_row, 0) matches “March”.

What if my table has multiple rows with the same lookup value?

VLOOKUP returns the first match. If you need all matches, use FILTER (in modern Excel/Sheets) or an array formula.

Is there a performance difference between VLOOKUP and INDEX/MATCH?

INDEX/MATCH is generally faster on large datasets because VLOOKUP reads the entire table array. XLOOKUP is also efficient. For small tables, the difference is negligible.

Related

• DIRECTV NOW & AT&T TV: How Many Streams?
• Why I Never Update My 3D Printer Firmware Right Away
• Operation Southern Spear Expense: Full Cost Breakdown

in row | Row lookup value | - | 3 | 0
in col | Column lookup value | - | 2 | 0
out result | Intersection value | - | (row-1)*3 + col | 0