问题描述:

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
r.Offset(0, 1).Value = "not found"
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.