Need help with Excel formula!!

N

Need formula!!

On worksheet tab 1, I have a list of numbers (col a) ranging from one to
twenty and a dollar value (col b) assigned to each. On the next
worksheet tab I have a list of names in col a and when I put a number
on the next column, I want col c to go to the first worksheet and give
me the corresponding dollar value assigned to that number. I am trying
to use the IF function, but with 20 different values, isn't there
anything simpler?
=IF(a1=1,sheet1!b1,IF(a1=2,sheet1!b2,IF(a1=3,sheet1!b3...........................
Isn't there a better way?
If not, how does this end?
 
B

Bernard Liengme

Let's assume the first 'tab' is in fact Sheet1, and that there are values in
A1:A100
On Sheet2 in A1 you have a name, in B1 you have a number,
in C1 use =VLOOKUP(B1,Sheet1!A1:B8,2,FALSE)

Possible problem: the number in B1 on Sheet2 may no be present in the table
on Sheet1 and you will get #N/A. If you want the nearest match, use
=VLOOKUP(B1,Sheet1!A1:B8,2,TRUE) and sort the table in Sheet1 in ascending
order.

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Need formula!!" <[email protected]>
wrote in message
news:[email protected]...
 
K

Ken Wright

Take a look at the VLOOKUP function

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------


"Need formula!!" <[email protected]>
wrote in message
news:[email protected]...
 
Top