Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    How does it find the answer (Excel 2002)

    I have a workbook which contains the following

    =IF(B7<>"",VLOOKUP(B7,Chem_Table,PT_Price,FALSE)," ") the result inserts in the cell
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: How does it find the answer (Excel 2002)

    Based on what you wrote, It sounds like the "chemTable" is 6 columns wide and it is not looking for the 3rd column but the 6th column.

    =IF(B7<>"",VLOOKUP(B7,Chem_Table,<font color=red>6</font color=red>,FALSE)," ")

    Though that "6" could be variable.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: How does it find the answer (Excel 2002)

    Hi Steve

    Perhaps I wasn't very clear in my post, the result is correct, as you will see from the screenshot, =IF(B7<>"",VLOOKUP(B7,Chem_Table,PT_Price,FALSE)," ") the result is in column 3 in the table Chem_Table as in the screenshot, but the request is to look up PT_Price wich is in another worksheet as a named cell which contains the number 6.
    To recap if the command is to look up PT_Price whch is a named cell in a worksheet containing the number 6 how does it come up with the answer which in Chem_Table in column 3

    Curiouser and Curiouser said Alice

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: How does it find the answer (Excel 2002)

    Hi Steve

    I feel a bit of a fool you were correct the first time it is a variable which is contained in the cell named PT_Price.
    My apologies for wasting your time, However I will almost certainly be be back with other requests regarding this monstrous spreadsheet , as understanding and it and making changes will help to keep employed for a few days.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How does it find the answer (Excel 2002)

    Braddy,

    ChemTable looks sorted on its first column. If that is indeed the case, you'd be better off using:

    =IF(B7="","",IF(LOOKUP(B7,x!$A$2:$A$1000)=B7,LOOKU P(B7,x!$A$2:$C$1000),"")

    where x!$A$2:$C$1000 is ChemTable.

    Change the ranges to the true ranges and x to the sheet name that houses the table.
    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: How does it find the answer (Excel 2002)

    Hi Aladin

    Thanks for your reply, however as I said to Steve in my reply, I originally did not understand because I had not seen a lookup table using a variable before, but now I understand that I am happy to go with that as it achieves the correct result.. Haviing said that I notice that you use term LOOKUP and not VLOOKUP OR HLOOKUP, which is something else I have not seen before. But my knowledge is somewhat limited although I am getting better.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How does it find the answer (Excel 2002)

    I should have put it in terms of what you already have...

    =IF(B7="","",IF(VLOOKUP(B7,Chem_Table,1,1)=B7,VLOO KUP(B7,Chem_Table,PT_Price,1),""))

    which should fetch/retrieve prices much faster.

    The prerequisite is, as I said, that Chem_Table is sorted in ascending order on its first column.

    Note. My suggestion of course has nothing to do with your original question.
    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: How does it find the answer (Excel 2002)

    Hi Aladin

    Thanks for you response it is most appreciated.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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