ExcelBanter - VLOOKUP more than one instance

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - VLOOKUP more than one instance (https://www.excelbanter.com/excel-discussion-misc-queries/141140-vlookup-more-than-one-instance.html)

VLOOKUP more than one instance

Formula Below:

I am looking up a product number (Cell $A416 in formula below) and returning
its corresponding inventory count.

The problem I have is the product number may appear more than once in the
lookup range. My formula is only giving the first occurance - its not adding
the count for all instances.
Incidently, I get back alot on NA's, that is why I have the ISNA part.

Any help?

IF(ISNA(VLOOKUP($A416,'I631_2
sales'!$A$10:$D2671,4,FALSE)),"0",(VLOOKUP($A416,' I631_2
sales'!$A$10:$D2671,4,FALSE)))

VLOOKUP more than one instance

Try:

=SUMPRODUCT(--('I631_2 sales'!$A$10:$D2671=$A416),('I631_2
sales'!$D$10:$D2671))

tojo107" wrote:

Formula Below:

I am looking up a product number (Cell $A416 in formula below) and returning
its corresponding inventory count.

The problem I have is the product number may appear more than once in the
lookup range. My formula is only giving the first occurance - its not adding
the count for all instances.
Incidently, I get back alot on NA's, that is why I have the ISNA part.

Any help?

IF(ISNA(VLOOKUP($A416,'I631_2
sales'!$A$10:$D2671,4,FALSE)),"0",(VLOOKUP($A416,' I631_2
sales'!$A$10:$D2671,4,FALSE)))