Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A 'Lookup' Formula (Excel 2000)

    I would very much appreciate some help with a formula or IF function (or any advice on how to accomplish this task).

    I have an Excel workbook with 15+ worksheets, and need to work with two worksheets named

  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: A 'Lookup' Formula (Excel 2000)

    In J9 enter:
    =IF(ISNA(VLOOKUP(A9,Input!$A:$H,8,0)),0,VLOOKUP(A9 ,Input!$A:$H,8,0))

    copy this to J10:J27

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A 'Lookup' Formula (Excel 2000)

    Many thanks, Steve! I would never have been able to figure this out! I will try your formula this afternoon. Appreciate your quick response!...Mary

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A 'Lookup' Formula (Excel 2000)

    Steve, will the fact that the table in the Materials worksheet is a Pivot table make a difference? (I didn't realize it was until now.) I pasted the formula and even though there is a match in the Input worksheet with corresponding data, the result appears as a "0"? Thanks...Mary

  5. #5
    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: A 'Lookup' Formula (Excel 2000)

    It shouldn't matter whether or not it is a pivot table

    somethings to look for:
    The match must be identical
    The value in col H is not zero

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A 'Lookup' Formula (Excel 2000)

    Thanks again, Steve...I double checked the formula and data again and I had changed "H" to "I" in your formula because I gave you the incorrect column H from the "Input" worksheet. The formula with my change wasn't working. However, when I deleted the old "H" column, so that the previous "I" column became the "H" column, it worked beautifully and automatically the formula changed by to reference H. I wonder why it didn't take my manual change in the formula? Thanks again for sending me your formula--it now works great!...Mary

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A 'Lookup' Formula (Excel 2000)

    You have to change two things to pick up Column I instead of H in Steve's Formula. You have to change the column in the array from H to I as you did, but you also have to change the column number that VLOOKUP returns from 8 to 9. It would look like this:

    <pre>=IF(ISNA(VLOOKUP(A9,Input!$A:$I,9,0)),0,VLOOK UP(A9,Input!$A:$I,9,0))
    </pre>

    Legare Coleman

  8. #8
    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: A 'Lookup' Formula (Excel 2000)

    If you manually changed the H to I, did you also change the 8 to a 9?

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A 'Lookup' Formula (Excel 2000)

    Thank you, Steve and Legare...No, indeed, I'm embarrassed to say that I did not think to change the 8 to a 9--that was why it didn't work at first. Thanks again very much for the good information. I will file this useful function away for next time this need arises. ...Mary

Posting Permissions

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