|
Need help with Excel 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,sheet 1!b3........................... Isn't there a better way? If not, how does this end? -- Need formula!! ------------------------------------------------------------------------ Need formula!!'s Profile: http://www.excelforum.com/member.php...o&userid=26265 View this thread: http://www.excelforum.com/showthread...hreadid=395594 |
=VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
extend down the column Greetings from New Zealand Bill K "Need formula!!" wrote in message news:Need.formula.1tq3qc_1123970727.7672@excelforu m-nospam.com... 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,sheet 1!b3........................... Isn't there a better way? If not, how does this end? -- Need formula!! ------------------------------------------------------------------------ Need formula!!'s Profile: http://www.excelforum.com/member.php...o&userid=26265 View this thread: http://www.excelforum.com/showthread...hreadid=395594 |
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!!" wrote in message news:Need.formula.1tq3qc_1123970727.7672@excelforu m-nospam.com... 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,sheet 1!b3........................... Isn't there a better way? If not, how does this end? -- Need formula!! ------------------------------------------------------------------------ Need formula!!'s Profile: http://www.excelforum.com/member.php...o&userid=26265 View this thread: http://www.excelforum.com/showthread...hreadid=395594 |
=INDEX(Sheet1!$B$1:$B$20,A1)
Need formula!! wrote: 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,sheet 1!b3........................... Isn't there a better way? If not, how does this end? |
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!!" wrote in message news:Need.formula.1tq3qc_1123970727.7672@excelforu m-nospam.com... 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,sheet 1!b3........................... Isn't there a better way? If not, how does this end? -- Need formula!! ------------------------------------------------------------------------ Need formula!!'s Profile: http://www.excelforum.com/member.php...o&userid=26265 View this thread: http://www.excelforum.com/showthread...hreadid=395594 |
Hi!
Assume the numbers and dollar values are in Sheet1A1:B20. Sheet2A1 = Bill Sheet2B1 = 5 Sheet2C1 = formula: =IF(B1="","",VLOOKUP(B1,Sheet1!A$1:B$20,2,0)) Biff "Need formula!!" wrote in message news:Need.formula.1tq3qc_1123970727.7672@excelforu m-nospam.com... 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,sheet 1!b3........................... Isn't there a better way? If not, how does this end? -- Need formula!! ------------------------------------------------------------------------ Need formula!!'s Profile: http://www.excelforum.com/member.php...o&userid=26265 View this thread: http://www.excelforum.com/showthread...hreadid=395594 |
| All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com