I have two columns A and B in an Excel sheet, similar to the following:-

``A B1 12 23 44 55 66 77 88 109 1110 1211 1312 1513 1614 1715 18``

Now, in a different sheet, I have a column of B values, and I want to 'map' them to their corresponding A values. By 'map' them, I mean replace a B value with the A value that is adjacent to it in the first sheet. How do I do this?

Option 1)

In sheet2 column C you want your results and lets say and your B data is in column D just to mix things up.

``````=INDEX(SHEET1!\$A\$1:\$A\$15,MATCH(D2,SHEET1!\$B\$1:\$B\$15,0))
``````

Option 2)

Same setup but lets use the LOOKUP function

``````=LOOKUP(D2,SHEET1!\$B\$1:\$B\$15,SHEET1!\$A\$1:\$A\$15)
``````

With Sheet1 like:

and Sheet2 like:

Running this short macro:

``````Sub Translate()
Dim B As Range, RangeToFix As Range, r As Range
Dim fnd As Range

Set B = Sheets("Sheet1").Range("B1:B15")
Set RangeToFix = Sheets("Sheet2").Range("B1:B11")

For Each r In RangeToFix
Set fnd = B.Find(What:=r.Value, After:=B(1))
If fnd Is Nothing Then