Getting the result of #NA when using the Index and Match

[vmohan1978]

Item# Desc Qty TT Custom Manufacturing, LLC TOTAL HF Collections TOTAL

PF-1201 SOFA 1 $875.16 $875.16 $2,528.00 $2,528.00


There are around 15 supplier are there to compare the lowest rate and vendor name i am using

to find the minimum value : SMALL(D5:AG5,IF(MIN(D5:AG5)=0,2,1))

To fine corresponding supplier name i a using formula : INDEX($D$4:$AG$4,MATCH(AH5,D5:AG5))

When the Qty is 1 insted of getting the customer name i am getting result as TOTAL and some of the rows i am getting #NA as result please do the needful

 

[Luke M]

First, let's change your formula for minimum valus so that it ignores "all" values of 0.


=SMALL(D5:AG5,COUNTIF(D5:AG5,0)+1)


Next, we need to use a 0 in the 3rd arguement of the MATCH function to find the first exact match.


=INDEX($D$4:$AG$4,MATCH(AH5,D5:AG5,0))


Note that if all prices are 0, then formula will create an error (but maybe that would be a good thing to flag?)