CODEWITHSUNDEEP
excelvba
Shiva
Shiva

Reputation: 430

Excel VBA : Multiple lookup values

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.

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.

Upvotes: 0

Views: 489

Answers (1)

StandardDeviation
StandardDeviation

Reputation: 140

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.

Upvotes: 0

Related Questions