Excel VBA : Multiple lookup values

Question

I have two sheets. Result needed as in sheet1 Required Results column as depicted below. The results are populated by checking the values in sheet2.

Noun      Modifier  Required Results                        Name1   Value1  Name2   Value2  Name3   Value3  Name4      Value4      Name4    Value4
ABRASIVE    BELT    ABRASIVE BELT : 5in X 2in               TYPE    WAFER   WIDTH           LENGTH  5in     THICKNESS   2in       DIAMETER   2m
ABRASIVE    BELT    ABRASIVE BELT : 11in X 6in X 3m         TYPE    LUGGED  WIDTH   11in    LENGTH  6in     THICKNESS   3in       DIAMETER   3m
ABRASIVE    BELT    ABRASIVE BELT : 12in X 7in X 3m         TYPE    LUGGED  WIDTH   12in    LENGTH  7in     THICKNESS   3in       DIAMETER   4m

Sheet2

Noun      Modifier  Attribute Name    fill
ABRASIVE    BELT    TYPE                0
ABRASIVE    BELT    WIDTH               1
ABRASIVE    BELT    LENGTH              2
ABRASIVE    BELT    THICKNESS           3
ABRASIVE    BELT    DIAMETER            0
ABRASIVE    ROD     TYPE                0
ABRASIVE    ROD     LENGTH              1

I will explain the update process by taking first row as the example.

ABRASIVE word from sheet1 is searched in sheet2 Noun column.
If Matches corresponding next value BELT from sheet1 is searched in sheet2 Modifier column.
If both the values matches column Name1 value TYPE should be searched in sheet2 Attribute Name column. If fill column is 0 for the corresponding row, then no need to populate the value in sheet1. Here in sheet 2 WIDTH, LENGTH, THICKNESS the values are 1 in fill column. Therefore I have concatenated WIDTH, LENGTH, THICHNESS values as 5in X 2 in. IN first row Width is blank.

So referring to Sheet two, I need to populate the dimensions. I have around 10K records in sheet1 and 20K reference data in Sheet2. I tried applying several vlookup formulas, but I could not achieve it. Please help.

StandardDeviation StandardDeviation · Accepted Answer · 2014-04-23T16:44:07

One simple way to make an unique identifier using several identifiers is to add the first column to the both of the sheets containing formula:

="identifier-1"&"identifier-2"&...&"identifier-n"

where "identifier-1", "Identifier-2" and "identifier-n" are cell addresses containing respective identifiers.

Using VLOOKUP formula with unique identifiers, desired data can be retrieved.

After populating data in Sheet1 Value1 through Value4, data can be joined in Dimension column using CONCATENATE function or "&" for merging values.

Excel VBA : Multiple lookup values

1 Answers