Output value if another column meets a value

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!

Find more posts tagged with

Accepted answers

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.

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.