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!
Best Answer
-
[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.
Answers
-
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)
-
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?
-
[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.
-
In other words, the one thing I didn't try.
Thanks!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!