Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get address of named range in Excel
  • Merge tables with VLOOKUP in Excel
  • How to get last row in text data in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Offset in Excel

Data Analysis Examples

  • Conditional Formatting New Rule with Formulas in Excel
  • Randomize/ Shuffle List in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Get column index in Excel Table
  • How to add Trendline to a chart in Excel

Data Validation Examples

  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only
  • Excel Data validation require unique number
  • Data validation must not exist in list
  • Excel Data validation only dates between

Two-way lookup with INDEX and MATCH in Excel

by

This tutorial shows how to calculate Two-way lookup with INDEX and MATCH in Excel using the example below;

Formula

=INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1))

Explanation

To lookup in value in a table using both rows and columns, you can build a formula that does a two-way lookup with INDEX and MATCH.

Worked Example:   Categorize text with keywords in Excel

In the example shown, the formula in J8 is:

=INDEX(C6:G10,MATCH(J6,B6:B10,1),MATCH(J7,C5:G5,1))

Note that this formula is doing and “approximate match”, so row values and column values must be sorted.

How this formula works

The core of this formula is INDEX, which is simply retrieving a value from C6:G10 (the “data”) based on a row number and a column number.

=INDEX(C6:G10, row, column)

To get the row and column numbers, we use MATCH, configured for approximate match, by setting the 3rd argument to 1 (TRUE):

MATCH(J6,B6:B10,1) // get row number
MATCH(J7,C5:G5,1) // get column number

In the example, MATCH will return 2 when width is 290, and 3 when height is 300.

Worked Example:   Get first match cell contains in Excel

In the end, the formula reduces to:

=INDEX(C6:G10, 2, 3)
= 1800
Worked Example:   Left Lookup in Excel

Post navigation

Previous Post:

VLOOKUP from another workbook in Excel

Next Post:

VLOOKUP from another sheet in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to convert numbers to text using TEXT function in Excel
  • Remove unwanted characters in Excel
  • UNICODE function: Description, Usage, Syntax, Examples and Explanation
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • Find most frequent text within a range with criteria in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate working days left in month in Excel
  • How to Calculate Age in Excel
  • Calculate years between dates in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
  • PRICEDISC function: Description, Usage, Syntax, Examples and Explanation
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles