Скачать или смотреть Two-Way Lookup and Simpler than INDEX-MATCH

Two-way lookups in Excel enable users to accurately and efficiently retrieve specific values from a large dataset by simultaneously identifying both a specific row and a specific column within a table. Unlike simple one-dimensional lookups, two-way lookups take into account two criteria: one located in the row headers and another in the column headers, ensuring precise and contextual data extraction.
for Two-Way Lookup
This UDF works like INDEX(MATCH, MATCH) but in a simpler way.

VBA Code for Two-Way Lookup

Function TwoWayLookup(rowValue As Variant, colValue As Variant, TableRange As Range) As Variant
Dim rowIndex As Variant, colIndex As Variant

' Find row index in the first column
rowIndex = Application.Match(rowValue, TableRange.Columns(1), 0)
' Find column index in the first row
colIndex = Application.Match(colValue, TableRange.Rows(1), 0)

' If both are found, return the corresponding value
If Not IsError(rowIndex) And Not IsError(colIndex) Then
TwoWayLookup = TableRange.Cells(rowIndex, colIndex)
Else
TwoWayLookup = "Not Found"
End If
End Function
How to Use in Excel
Example Data (Crosstab Table)
A B C D
Name Jan Feb Mar
John 100 200 150
Sarah 120 220 180
Mike 130 250 200
Formula to Find a Value
To get Sarah’s sales in February, use this formula:

=TwoWayLookup("Sarah", "Feb", A1:D4)
🔹 Result: 220

Why Use This UDF?
✅ Simpler than INDEX-MATCH
✅ Dynamic and Flexible
✅ Works for Any Table Size