# Thread: A 'Lookup' Formula (Excel 2000)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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>

8. ## 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. ## 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
•