MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Mappimg one set of numbers to another


Posted by Brian on January 28, 2002 12:07 PM

I have a column of numbers (columnA).
They range in value from 1-40.
I want to change these using a 2x40 reference array (columnE&F) (E1 maps to F1, E2 to F2 and so on to E40 to F40),to produce another column (B) with some type of lookup or logical command.
So if A1 contains 28 for example then the mapping in the matrix at E28 will correspond to the value say 7 in F28.
Then in B1 number 7 will appear. And so on down the list.
How can I achieve this?
Brian
PS Thankyou to Ivan M. for assistance previously today.


Posted by Mark W. on January 28, 2002 12:12 PM

Use the VLOOKUP() worksheet function [nt]

Posted by Brian on January 28, 2002 1:05 PM

Re: Use the VLOOKUP() worksheet function [nt]

Mark W
I have struggled with VLOOKUP() for a while but can't get the references correct. The excel help does not seem intuitive or is it just me!
Can you expand on the answer please? Thank you.
Brian


Posted by Mark W. on January 28, 2002 1:18 PM

Re: Use the VLOOKUP() worksheet function [nt]

Okay, you've 1 thru 40 in cells A1:A40 and E1:E40.
For discussion purposes let's say that F1:F40 contains...
10 thru 205 (increments of 5). Enter the formula,
=VLOOKUP(A1,$E$1:$F$40,2,0), into cell B1 and copy
down to B40. The values in column A are now "mapped"
to the values in column F.