Lookup

[A_SIRAT]

Hi Guys,

I am looking for one formula that will lookup the Product Name in Column F from the entire TABLE A and upon finding it, gives me the quantity shipped.

Thanks in advance.

[#2 · Accepted solution]

@A_SIRAT 

As variant

=IFERROR(INDEX( ($B$5:$B$10,$D$5:$D$10),IFNA(MATCH($F5,$A$5:$A$10,0),MATCH($F5,$C$5:$C$10,0)),,IF(ISNA(MATCH($F5,$A$5:$A$10,0)),2,1)),0)

 

[#3]

Thank you so much!

[#4]

@A_SIRAT , you are welcome

[#5]

@A_SIRAT 

If would also be possible to exploit the fact that the quantity shipped is always in a cell one to the right of the product.  Thus, if ProductTable is the first table, one can define QuantityTable to be the offset range

= OFFSET( ProductTable , 0, 1 )

For a list ProductName, the required quantity is returned by a SUMIFS operating over the two overlapping 2D ranges

= SUMIFS( QuantityTable, ProductTable, ProductName )