+ Reply to Thread
Results 1 to 96 of 96

Need help with Excel formula!!

  1. #1
    Registered User
    Join Date
    08-13-2005
    Posts
    1

    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,sheet1!b3...........................
    Isn't there a better way?
    If not, how does this end?

  2. #2
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  3. #3
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  4. #4
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  5. #5
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  6. #6
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  7. #7
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  8. #8
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  9. #9
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  10. #10
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  11. #11
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  12. #12
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  13. #13
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  14. #14
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  15. #15
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  16. #16
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  17. #17
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  18. #18
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  19. #19
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  20. #20
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  21. #21
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  22. #22
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  23. #23
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  24. #24
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  25. #25
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  26. #26
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  27. #27
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  28. #28
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  29. #29
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  30. #30
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  31. #31
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  32. #32
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  33. #33
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  34. #34
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  35. #35
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  36. #36
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  37. #37
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  38. #38
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  39. #39
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  40. #40
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  41. #41
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  42. #42
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  43. #43
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  44. #44
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  45. #45
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  46. #46
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  47. #47
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  48. #48
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  49. #49
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  50. #50
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  51. #51
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  52. #52
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  53. #53
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  54. #54
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  55. #55
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  56. #56
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  57. #57
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  58. #58
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  59. #59
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  60. #60
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  61. #61
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  62. #62
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  63. #63
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  64. #64
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  65. #65
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  66. #66
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  67. #67
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  68. #68
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  69. #69
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  70. #70
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  71. #71
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  72. #72
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  73. #73
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  74. #74
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  75. #75
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  76. #76
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  77. #77
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  78. #78
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  79. #79
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  80. #80
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  81. #81
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  82. #82
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  83. #83
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  84. #84
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  85. #85
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  86. #86
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  87. #87
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  88. #88
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  89. #89
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  90. #90
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  91. #91
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  92. #92
    Ken Wright
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  93. #93
    Bernard Liengme
    Guest

    Re: Need help with Excel formula!!

    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]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  94. #94
    Biff
    Guest

    Re: Need help with Excel formula!!

    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!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




  95. #95
    Aladin Akyurek
    Guest

    Re: Need help with Excel formula!!

    =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,sheet1!b3...........................
    > Isn't there a better way?
    > If not, how does this end?
    >
    >


  96. #96
    Bill Kuunders
    Guest

    Re: Need help with Excel formula!!

    =VLOOKUP(B1,Sheet1!$A$1:$B$20,2,FALSE)
    extend down the column

    Greetings from New Zealand
    Bill K


    "Need formula!!" <[email protected]>
    wrote in message
    news:[email protected]...
    >
    > 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?
    >
    >
    > --
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1