Forum Discussion
Solved
Lookup
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.
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)
4 Replies
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 )
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)- Thank you so much!
A_SIRAT , you are welcome
