1

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.

Ram
  • 3,092
  • 10
  • 40
  • 56
Mr.Robot
  • 489
  • 2
  • 8
  • 17

4 Answers4

3

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))
SuperBiasedMan
  • 9,814
  • 10
  • 45
  • 73
Cosmin S.
  • 31
  • 7
1

My solution to this is to put both sheets into MS Access and do a join query. Especially if the tables are large. That's how I've done it many times and it is why I always get a version of Office that includes MS Access. Takes a couple of minutes and it is worth the effort.

K17
  • 697
  • 3
  • 8
  • 26
1

I got my solution after working with VBScript.

Private Sub GetID_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDefaultID = ThisWorkbook.Sheets.Item("DefaultIDs")
Set objGetID = ThisWorkbook.Sheets.Item("GetIDs")
iDefaultRC = objDefaultID.UsedRange.Rows.Count
iDefaultCC = objDefaultID.UsedRange.Columns.Count
iGetRC = objGetID.UsedRange.Rows.Count
iGetCC = objGetID.UsedRange.Columns.Count
For i = 1 To iGetCC
    If objGetID.Cells(1, i) = "Name" Then
        iGetNameCol = i
    ElseIf objGetID.Cells(1, i) = "ID" Then
        iGetIDCol = i
    End If
Next
For i = 1 To iDefaultCC
    If objDefaultID.Cells(1, i) = "Name" Then
        iDefNameCol = i
    ElseIf objDefaultID.Cells(1, i) = "ID" Then
        iDefIDCol = i
    End If
Next
For i = 2 To iGetRC
    For j = 2 To iDefaultRC
        If objGetID.Cells(i, iGetNameCol) = objDefaultID.Cells(j, iDefNameCol) Then
            objGetID.Cells(i, iGetIDCol) = objDefaultID.Cells(j, iDefIDCol)
        End If
    Next
Next

End Sub
halfer
  • 19,824
  • 17
  • 99
  • 186
Mr.Robot
  • 489
  • 2
  • 8
  • 17
0

What I would do in this situation is just switch the fields in one sheet so they are in the same order, then perform a vlookup. I would do this because I have the ability to tamper with the header orders. Don't know if you do also, but it may be best to keep everything organized.

If(isna(vlookup(A2,'Sheet1',A:B,2,FALSE)),"",vlookup(A2,'Sheet1',A:B,2,FALSE))

*after switching the header order in sheet1 to name on first column, then id on second column

Phil
  • 73
  • 3
  • 9
  • Yea that works also, but to avoid doing the manual work every time, you can switch the way the data is exported. Where are you pulling the data from? – Phil Oct 24 '15 at 17:21
  • I have a raw data into excel and another sorting has to be done from that data . So i posted this question. Please suggest me the formula for above data . @phil – Mr.Robot Oct 24 '15 at 17:49
  • Here you can find what you need. It's showing how to perform an index match to get the results you need. On cell so can't use multiple screens otherwise would help with formula. http://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup#sthash.kHKkDE62.dpbs – Phil Oct 24 '15 at 17:54
  • @phil I dont have time to make the research . I will be happy if you would help me out with direct formulae – Mr.Robot Oct 24 '15 at 18:03
  • All you have to do is click on link – Phil Oct 24 '15 at 19:14