Two dimensional lookup with INDEX and MATCH
This tutorial shows how to apply a two dimensional lookup using a combination of the Excel INDEX and MATCH functions
Example: Two dimensional lookup with INDEX and MATCH
|
The formula uses the Excel INDEX and MATCH functions to return the value that is associated with Shop B and Milk. The formula uses the INDEX function to select the data and then applies the MATCH function to acquire the row and column numbers associated with Shop B and Milk, respectively, relative to the selected range.
|
Dim ws As Worksheet
ws.Range("G6").Value = Application.WorksheetFunction.Index(ws.Range("C5:D9"), Application.WorksheetFunction.Match(ws.Range("G4"), ws.Range("B5:B9"), 0), Application.WorksheetFunction.Match(ws.Range("G5"), ws.Range("C4:D4"), 0))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Index Range: If using the exact VBA code ensure that the range ("C5:D9") captures the data that you want to return.
Match Row Range: If using the exact VBA code ensure that the range ("B5:B9") captures one of the values that you are looking up for which you want to return the row number, relative to the range. In this example range ("B5:B9") captures value "Shop B" which is one of the criteria that we are looking up.
Match Column Range: If using the exact VBA code ensure that the range ("C4:D4") captures one of the values that you are looking up for which you want to return the column number, relative to the range. In this example range ("C4:D4") captures value "Milk" which is one of the criteria that we are looking up.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("G6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Index Range: Select the data range by changing range reference ("C5:D9") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Match Row Range: Select a range that is represented by one column and across multiple rows that captures one of the values that you are looking up for which you want to return the row number by changing range ("B5:B9") relative to the data range.
Match Row Range: Select a range that is represented by one row and across multiple column that captures one of the values that you are looking up for which you want to return the column number by changing range ("C4:D4") relative to the data table.
Explanation about how to apply a two dimensional lookup using a combination of the Excel INDEX and MATCH functions
EXPLANATION
To apply a two dimensional lookup we can use a combination of the Excel INDEX and MATCH functions.
=INDEX(data_range,MATCH(row_lookup_value,row_lookup_range,0),MATCH(column_lookup_value,column_lookup_range,0))
ARGUMENTS
data_range: A table of data from which to lookup the value.
row_lookup_value: The value you want to lookup in a column range that will return the row number of the lookup value, relative to the range.
column_lookup_value: The value you want to lookup in a row range that will return the column number of the lookup value, relative to the range.
row_lookup_range: A range that comprises a single column and multiple rows, which captures the lookup value for which you want to return the row number, relative to the range.
column_lookup_range: A range that comprises a single row and multiple columns, which captures the lookup value for which you want to return the column number, relative to the range.