# excel - Replacing values in a column according to a map

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

A B

1 1

2 2

3 4

4 5

5 6

6 7

7 8

8 10

9 11

10 12

11 13

12 15

13 16

14 17

15 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
Else
r.Value = fnd.Offset(0, -1).Value
End If
Next r
End Sub

will Produce this in Sheet2:

This does the "translation" in-place.