Index Match with a greater than or equal to function

[JWY MQ]

Hello friends,
I come to you in a time of need. I am attempting to provide an availability date(column G) for any given customer order(column B). I have my production schedule on the right, and was
hoping for some help on the formula in (column G). I have gathered from other posts that an INDEX MATCH is probably the correct way to go, but being a VLOOKUP user I can't quite get
one to work. The gist would be if F2 is less than or equal to K2, spit out I2. If not spit out the date in column I that is on the same row as the value in column K that is greater than F2. Hopefully that makes sense.

Thank you in advance for any help. This is such a helpful forum

 

[Eric W]

Welcome to the MrExcel forum!

Try:

=XLOOKUP(F2,$K$2:$K$1000,$I$2:$I$1000,"error",-1,1)

 

[JWY MQ]

Welcome to the MrExcel forum!

Try:

=XLOOKUP(F2,$K$2:$K$1000,$I$2:$I$1000,"error",-1,1)

Thank you for the quick reply Eric, copied it in, and currently getting the very last date(3/27/23) in the I column very each line:

 

[Anthony47]

Maybe

=IF(F2<=K2,I2,INDEX($I$1:$I$1000,MIN(IF(F2<$K$2:$K$1000,ROW($K$2:$K$1000),""))))

 

[Eric W]

These lookup functions can be tricky, how about:

Book1
	F	G	H	I	J	K
1	# in line	Availability		Date		Total Build
2	1	5/3/2022		5/3/2022		144
3	2	5/3/2022		5/4/2022		184
4	150	5/4/2022		5/18/2022		232
5	232	5/18/2022		5/19/2022		280
6	300	5/23/2022		5/23/2022		328
7	400	5/25/2022		5/24/2022		376
8	423	5/25/2022		5/25/2022		424
9	424	5/25/2022		6/6/2022		472
10	425	6/6/2022		6/7/2022		520
11	500	6/7/2022		6/21/2022		568
Sheet3

Cell Formulas
Range	Formula
G2:G11	G2	=MINIFS($I$2:$I$11,$K$2:$K$11,">="&F2)

 

[JWY MQ]

These lookup functions can be tricky, how about:

You are a legend sir, that nailed it.

 

[Eric W]

Glad we could help!