Forum Discussion
Fill in a cell based on an answer from another range
As a General Contractor I am reviewing subcontractors/pricing... I have a list of prices in Row1 and a list of Contractor Names in Row2 I have a separate tab showing lowest price per trade. I use '...
Use XLOOKUP to find the lowest price and return the contractor directly beneath it.
For example, if your source tab is named Quotes, with prices in B1 and contractor names in B2:H2:
=XLOOKUP(MIN(Quotes!B1:H1),Quotes!B1:H1,Quotes!B2:H2)
Put that in the contractor-name cell on your summary sheet. Replace the sheet name and ranges with yours.
If two contractors have the same lowest price, XLOOKUP returns the first. To list all tied contractors in one cell, use:
=TEXTJOIN(", ",TRUE,FILTER(Quotes!B2:H2,Quotes!B1:H1=MIN(Quotes!B1:H1)))
Make sure missing bids aren’t entered as zero, since MIN would treat zero as the lowest price.