问题描述:

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.

相关阅读:
Top