If/vlookup statement need help

  • Thread starter Thread starter William Poh Ben
  • Start date Start date
W

William Poh Ben

Hi,


If vlookup had found Quantity to be zero for PartNumber 01235-66212,
then I want vlookup to continue to look up to the same PartNumber that
has prefix "W" behind (ie. 01235-66212W) for its Quantity.

Appreciate anyone who can help me with the formula ?
Thanks a bunches in advance !


PartNumber Quantity
01223-50200 3
01235-66212 0
01235-66212W 5
 
William Poh Ben said:
Hi,


If vlookup had found Quantity to be zero for PartNumber 01235-66212,
then I want vlookup to continue to look up to the same PartNumber that
has prefix "W" behind (ie. 01235-66212W) for its Quantity.

Appreciate anyone who can help me with the formula ?
Thanks a bunches in advance !


PartNumber Quantity
01223-50200 3
01235-66212 0
01235-66212W 5
Click to expand...

One way:
Suppose your part numbers were in A2:A4, your quantities in B2:B4, and the
part number you want to look up in C1.
=IF(SUMPRODUCT((A2:A4=C1)*B2:B4)=0,VLOOKUP(C1&"W",A2:B4,2,0),VLOOKUP(C1,A2:B
4,2,0))
 
Back
Top