VLookup Formula for Inventory count with different/multiple Values

In D2, copied down:

=LOOKUP(2, 1/(ISNUMBER(SEARCH($A$2:$A$10, $C2))), $B$2:$B$10)


Note, by the nature of the "fuzzy" match ability of LOOKUP, if matches are found in column A to more than row, it will return the match of the LAST one found. So a string of ABC-GHI would match to GHI.

Here is an another alternative...

Formula:

Please Login or Register  to view this content.

check the attached file...

Or this

=LOOKUP(25^25,SEARCH($A$2:$A$10,C2),$B$2:$B$10)

Hi, May be this

Formula:

Please Login or Register  to view this content.

Boopathiraja - that formula would SUM all the strings that were inadvertently matched instead of just one, no doubt in an inventory situation you would never want that to happen. See my final note in post #2. In your formula it would SUM AAA and GHI, a bad result in my expectation.

@ JBeaucaire, I agree, But my understanding was What if he might have the same product with different batches in different rows in where he need to sum it up to result the available Products,

But i would have posted a note on this thanks for the notification