Showing who is the lowest bidder

[broncos347]

I have the table below where I have recorded three bidders that have been returned for a portion of the work I need to undertake. From the information in the table I would like to see who is the lowest bidder in column F, is there a formula that I could use to show who is the lowest bidder and what their bid was?

Survey Log.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Survey Type	Description	Return Date	Winning Bidder	Consultant 1				Consultant 2				Consultant 3				Consultant 4
2					Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal
7	Structural Survey	Structural survey of footbridge soffit	22/07/2024		ano1	15/07/2024	22/07/2024	£ 12,264.00	ano2	15/07/2024	22/07/2024	£ 4,030.00	ano3	15/07/2024	19/07/2024	£ 3,715.00
Sheet1

Cell Formulas
Range	Formula
N7	N7	=2450+1580

 

[awoohaw]

There may be some shorter methods, but this seems to work:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Survey Type	Description	Return Date	Winning Bidder	Consultant 1				Consultant 2				Consultant 3				Consultant 4
2					Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal
3
4
5
6
7	Structural Survey	Structural survey of footbridge soffit	2024-07-22	ano3	ano1	2024-07-15	2024-07-22	£ 12,264.00	ano2	2024-07-15	2024-07-22	£ 4,030.00	ano3	2024-07-15	2024-07-19	£ 3,715.00
Sheet2

Cell Formulas
Range	Formula
D7	D7	=LET(d,$E$7:$T$7,b,CHOOSECOLS(d,1,5,9,13),a,CHOOSECOLS(d,4,8,12,16), la,MIN(a),lb,XLOOKUP(la,a,b,"No Bid"),lb)
L7	L7	=2450+1580

 

[Eric W]

Another option:

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Survey Type	Description	Return Date	Winning Bidder	Consultant 1				Consultant 2				Consultant 3				Consultant 4
2					Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal	Who?	Issued	Returned	Fee Proposal
7	Structural Survey	Structural survey of footbridge soffit	7/22/2024	ano3	ano1	7/15/2024	7/22/2024	$12,264.00	ano2	7/15/2024	7/22/2024	$ 4,030.00	ano3	7/15/2024	7/19/2024	$ 3,715.00
Sheet7

Cell Formulas
Range	Formula
F7	F7	=XLOOKUP(MIN(J7,N7,R7,V7),J7:V7,G7:S7,"No bid",0)
N7	N7	=2450+1580

To be fair, this is essentially the same as awoohaw's formula, but his also prevents the lowest bid from potentially matching a date.

 

[broncos347]

Many thanks for both formulas.