How to map the sheet 1 data with sheet 2 with help of Unique ID?

excel lookup vlookup excel-match
Mr.Robot picture Mr.Robot · Oct 24, 2015 · Viewed 16.4k times · Source

I have created an excel sheet with the attributes ID, NAME in one sheet.

SHEET 1:

enter image description here

In another sheet, I have names in a random manner and they have to be mapped to their correct ID with the reference from SHEET-1 data.

SHEET 2:

enter image description here

The highlighted values are manually entered. Is there any formula that can populate the data automatically.

NOTE: The above data is sample set of data and original data differs in sheet 2. There will be more than 2 columns in the second sheet.

Answer

Cosmin S. picture Cosmin S. · Aug 11, 2016

Here is a VERY generic formula for lookup between sheets. It looks for the key from column A in column A from the lookup sheet and returns the value on the column with the same name as the current column (title is on row 7).The lookup key has to be on the left of the lookup value, or vlookup doesn't work:

=VLOOKUP($A:$A,'Lookup sheet'!$A:$ZZ,MATCH($7:$7,'Lookup sheet'!$7:$7,0),FALSE)

Alternatively If the value (in A:A) is on the left of the key (in D:D) in the lookup sheet, you need to know the column of the value:

=INDEX('Lookup sheet'!$A:$A,MATCH($E:$E,'Lookup sheet'!$D:$D,0))

Related questions

Lookup a value if the dates fall between two dates

I have window dates in two cells. For example: a1 = 1/1/2014 8:00 b1 = 1/4/2014 10:00 c1 = 11 Where A is the start date B is the end date C is the code for the window In cell d1 I have the date: 1/3/2014 7:00 I would like …

Read More
Lookup using INDEX and MATCH with two criteria

I am trying to achieve a basic lookup using INDEX and MATCH. My layout is: Sheet 1 NAME | SITE | DATE Sheet 2 NAME | SITE | DATE I want the 'SITE' column in Sheet 1 to automatically populate with the SITE from Sheet 2 where NAME …

Read More
How to concatenate values in multiple cells based on a condition?

I have a need to search a row of cells, and for every cell that contains a specific value, return the value from the cell above. For example, consider the following +---+--------+--------+--------+--------+--------+----------+ | | A | B | …

Read More