Two dimensional lookup with VLOOKUP and HLOOKUP
This tutorial shows how to apply a two dimensional lookup using a combination of the Excel VLOOKUP and HLOOKUP functions
Example: Two dimensional lookup with VLOOKUP and HLOOKUP
|
The formula uses the Excel VLOOKUP and HLOOKUP functions to return the value that is associated with Shop B and Milk. The formula initially looks up the row that contains Shop B in range (B6:B10), which is achieved with a VLOOKUP function. The VLOOKUP function then requires the relevant column number of the selected range, that is associated with Milk. This is achieved with a HLOOKUP function which looks up the value Milk is range (B4:D4) and returns the value that sits below it which represents the column number of the selected range.
|
Dim ws As Worksheet
ws.Range("G6").Value = WorksheetFunction.VLookup(ws.Range("G4"), ws.Range("B6:D10"), WorksheetFunction.HLookup(ws.Range("G5"), ws.Range("B4:D5"), 2, False), False)
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.
VLookup Range: If using the exact VBA code ensure that the range ("C6:D10") captures the data that you want to return and range ("B6:B10") captures one of the values that you are looking up.
HLookup Range: If using the exact VBA code ensure that the range ("B4:D4") captures the value that you 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.
VLookup Range: Select the data range and the range that captures one of the values that you are looking up by changing range reference ("B6:D10") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
HLookup Range: Select the range that captures the other value by changing range reference ("B4:D4") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
Explanation about how to apply a two dimensional lookup using a combination of an Excel VLOOKUP and HLOOKUP functions
EXPLANATION
To apply a two dimensional lookup we can to apply a combination of the Excel VLOOKUP and HLOOKUP functions.
=VLOOKUP(lookup_value_1,table_1,HLOOKUP(lookup_value_2,table_2,2,FALSE),FALSE)
ARGUMENTS
lookup_value_1: The value you want to lookup in the first column from the selected range (table).
lookup_value_2: The value you want to lookup in the first row from the selected range (table)
table_1: A table of data from which to lookup the value. The first column of this table will also capture lookup_value_1.
table_2: A table comprising two rows. First row comprises the values that contain the lookup_value_2 and the second row will be the relevant column numbers, starting from the column that contains lookup_value_1.