Vlookup issue

**philiasfogg** · 10-23-2009, 11:46 AM

I have the attached workbook.
I have one sheet (materials) using Vlookup onto a second sheet (price list).

If I put in a qty against 2 or more items with the same part code on the price list only the first item is displayed. It does get shown multiple times.

I need to try and get all items displayed, even if the part code is the same.

Please look at attached workbook with sample already shown.

Thanks for your help

**NBVC** · 10-23-2009, 12:02 PM

Because your Price List database is large, try not to use array formulas.. so instead add a helper column in column I... use formula in I11: =IF(ISNUMBER(F11),MAX(I$10:I10)+1,0)

and copy right down.

Then in Materials sheet...C20, use:

=IF(ROWS($A$1:$A1)>$C$18,"",INDEX('price list'!B$5:B$6674,MATCH(ROWS($A$1:$A1),'price list'!$I$5:$I$6674,0)))

and copy across to column F

you will need just change the indexed ranges in E and F to get proper info..

Then copy all down.

**ron2k_1** · 10-23-2009, 12:11 PM

NBVC is 100% correct. You'll get a faster loading of the page and less buggy sheet if you don't use arrays. The index matching combination will do the trick for both data requirements, now if you are afraid of changing that you can just place this formula on F20
=IF($D20="","",SUMPRODUCT(--('price list'!$C$13:$C$6674=$D20),'price list'!$F$13:$F$6674))

and copy down.

Ron

**JBeaucaire** · 10-23-2009, 12:15 PM

To eliminate the need for the hefty arrays, I added an indexing key to your "pricelist" in column I. You can hide that column if you wish.

Now, the items are brought into the estimate by that index, in order, rather than by array evaluation. So the sheet will never experience any performance issue AND all the items will transfer over based solely on whether there is a QTY or not.

All the formulas are now simple INDEX/MATCH.

EDIT:
Late to the party, I see that my answer matches NBVC with the exception I wouldn't use MAX() over and over in column I as that keeps evaluating the whole column all over again and all you need to look at is the one cell above.

I can't see an advantage to using SUMPRODUCT() over INDEX/MATCH.