Results 1 to 9 of 9
Thread: A 'Lookup' Formula (Excel 2000)

20040512, 17:36 #1
 Join Date
 Jan 2003
 Posts
 199
 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

20040512, 18:11 #2
 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

20040512, 18:27 #3
 Join Date
 Jan 2003
 Posts
 199
 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

20040512, 18:37 #4
 Join Date
 Jan 2003
 Posts
 199
 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

20040512, 18:49 #5
 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

20040512, 19:40 #6
 Join Date
 Jan 2003
 Posts
 199
 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 formulait now works great!...Mary

20040512, 20:38 #7
 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

20040512, 20:39 #8
 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

20040512, 20:46 #9
 Join Date
 Jan 2003
 Posts
 199
 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 9that 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