Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Move chart to a separate worksheet in Excel
  • Excel Frequency Function Example
  • Get column name from index in Excel Table
  • Create Scatter Chart in Excel
  • How to create dynamic reference table name in Excel

References

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Multi-criteria lookup and transpose in Excel
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel
  • Perform case-sensitive Lookup in Excel

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation date in next 30 days
  • Excel Data validation must contain specific text
  • Excel Data validation whole percentage only
  • 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:   How to calculate two-way lookup VLOOKUP in Excel Table

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:   Position of first partial match in Excel

In the end, the formula reduces to:

=INDEX(C6:G10, 2, 3)
= 1800

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation 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

Logical Functions

  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Check multiple cells are equal in Excel
  • Invoice status with nested if in Excel
  • SWITCH function example in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Get project end date in Excel
  • How to get same date next month or previous month in Excel
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • Convert text to date in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Group numbers with VLOOKUP in Excel
  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to add sequential row numbers to a set of data in Excel
  • Create dynamic workbook reference to another workbook in Excel
  • How to get random value from list or table in Excel
  • Check if range contains a value not in another range in Excel
  • How to generate random times at specific intervals in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning