Search code examples
excelexcel-2007

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?

Solution

  • 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)