Output value if another column meets a value

JeffG_WI

Hello,

I am making a bid evaluation suite. It is made up of several separate sheets for each type of bid and those are supposed to feed to a main page that shows the lowest bid and that bidder only if it is a complete bid. Here is the basic set up:

So, the front page where all the lowest bids are shown should only return Vendor A since it is the lowest complete bid.

I have the "=SMALL({Range},1)" to show the lowest number and the "=INDEX({Range},MATCH(SMALL({Range},1),{Range},0))" functions working but I can't figure out the syntax to add in the condition that the "Complete Bid?" column be "Yes".

Thanks!

Paul Newcome

[Column housing the lowest $ amount]@row needs to be the name of the column in your sheet that houses the lowest amount. Basically the column name where the first formula is.

Paul Newcome

Try a MIN/COLLECT and INDEX/COLLECT

=MIN(COLLECT({Amount}, {Complete?}, @cell = "Yes"))

=INDEX(COLLECT({Bidder}, {Amount}, @cell = [Column housing the lowest $ amount]@row, {Complete?}, @cell = "Yes"), 1)

JeffG_WI

Thanks Paul,

The first solution works like a charm. However, the second is coming up as unparseable. I tried entering it literally the same using [Column housing...] and also using {Amount}. I tried to get creative and mess around with the formula and even try to work it with my original MATCH direction.

What am I missing?

Paul Newcome

[Column housing the lowest $ amount]@row needs to be the name of the column in your sheet that houses the lowest amount. Basically the column name where the first formula is.

JeffG_WI

In other words, the one thing I didn't try.

Thanks!

Paul Newcome

Happy to help. 👍️