Lookup & Referenceadvanced
Two-way lookup with INDEX and MATCH
=INDEX(B2:E10,MATCH(H2,A2:A10,0),MATCH(H3,B1:E1,0))
Function reference
INDEX in Excel & Google Sheets
INDEX is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.
1 advancedBuild a custom INDEX formula →
When to use INDEX
- Find the price for a specific product and size from a pricing matrix, or retrieve a quarterly budget for a given department and quarter.
Worked examples (1)
Common errors with INDEX
MATCH returns
The lookup value does not exist in the header row or column. Check for extra spaces (use TRIM), mismatched case, or verify the lookup value is in the correct range.
Wrong range for the data body
The INDEX range must not include the header row or column — start it one cell inside the headers so row and column positions align correctly.
Frequently asked questions
Why use INDEX/MATCH instead of VLOOKUP for two-way lookups?
VLOOKUP can only look up by row, not by column. INDEX/MATCH is the standard solution for intersection lookups because both MATCH calls work independently.
Need a custom variation?
Describe your specific INDEX problem and get a formula written for your exact data.
Open generator with INDEX