LOOKUP VENDOR NAME w/ LOWEST BID. EXCLUDE ZERO AND TEXT

[Oranjin]

I have a sheet with the lowest bidders for three columns. I have written a formula that is searching for the lowest value, and then indexing the header of the column w/ the lowest value, and putting the text in the cell (where the formula is.) The problem is, it's giving me a "000" per my instructions, if the value is not found

I need this formula to grab the column header for the vendor that has provided the lowest bid. There are some conditions that it must work under. These are:

• It must ignore zeros. It must NOT Provide an output for the a vendor that has provided 0 next to two vendors whose values are say 1,2
• If there are multiple text values, AND zero AND a low value, IT MUST PROVIDE THE LOW VALUE (AS IN THIS CASE IT IS THE ONLY VALUE)
• If there is only 1 value, it should provide that value's header, REGAURDLESS of whether there is text or other nonsense in it.

I believe what I have is close. Please let me know if I can help.

PRODUCT	V1	V2	V3	V4	OUTPUT
111	1	2	3	4
222	3	#N/A	3	4
333	2		#N/A	2	000
444	#N/A	2			000

<tbody>
</tbody>

=IFERROR(INDEX($I$2:$K$2,,MATCH(MIN(I106:K106),I106:K106,0)),"000")

 

[Scott T]

Try

=INDEX(B$1:E$1,MATCH(AGGREGATE(15,6,B2:E2,1+COUNTIF(B2:E2,0)),B2:E2,0))

In the case of multiple lowest bid this will return the first vender. For example product 222 V1 and V3 both bid 3 which is the lowest this formula will return V1.

 

[Oranjin]

IF all the values are zero in the row in question, how can I make it say, "No Bid". For rows that are all zero, it says, #/NUM!

Which i suppose is reasonable because you've got it ignoring zeros so when there is no value...

 

[Scott T]

Try

=IF(AGGREGATE(9,6,B2:E2)=0,"No Bid",INDEX(B$1:E$1,MATCH(AGGREGATE(15,6,B2:E2,1+COUNTIF(B2:E2,0)),B2:E2,0)))

 

[eke_rula]

IF all the values are zero in the row in question, how can I make it say, "No Bid". For rows that are all zero, it says, #/NUM!

Which i suppose is reasonable because you've got it ignoring zeros so when there is no value...

Other way, you this formula:

=IFERROR(INDEX(B$1:E$1,MATCH(AGGREGATE(15,6,1/(1/B2:E2),1),B2:E2,)),"No bid")