Thread: Vlookup to ingnore missing values

Hi,
I want to look up a list using a vlookup. I have about 200 cars to do and each car has about 4000 items.

I need to vlookup and sum up a short list of car parts. My issue is of the 10 parts I am looking up, most cars dont contain all 10, rather 9 or 8.

How do I get a vlookup to ignore the value if one of the vlookup values is missing, rather than give me a blank.
Say for example, if my car doesnt have item number 1891 but has all the other 9, i still want a total?

=SUM
((VLOOKUP(1891,$C$2:$G$244,5,FALSE)+
(VLOOKUP(2470,$C$2:$G$244,5,FALSE)+
(VLOOKUP(2600,$C$2:$G$244,5,FALSE)+
(VLOOKUP(2620,$C$2:$G$244,5,FALSE)+
(VLOOKUP(2640,$C$2:$G$244,5,FALSE)+
(VLOOKUP(2670,$C$2:$G$244,5,FALSE)+
(VLOOKUP(3080,$C$2:$G$244,5,FALSE)+
(VLOOKUP(3083,$C$2:$G$244,5,FALSE)+
(VLOOKUP(3110,$C$2:$G$244,5,FALSE)+
(VLOOKUP(3171,$C$2:$G$244,5,FALSE))))))))))))))))+0.94

The answer I get is #N/A?

Try listing those item numbers in say B1:B10 and then apply formula like this:

=SUMPRODUCT(--ISNUMBER(MATCH($C$2:$C$244,$B$1:$B$10,0)),$G$2:$G$244)+0.94