Index Match with a greater than or equal to function

JWY MQ

New Member
Joined
May 4, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
1651684399573.png


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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,498
Welcome to the MrExcel forum!

Try:

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

JWY MQ

New Member
Joined
May 4, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel forum!

Try:

Excel Formula:
=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:
1651688872459.png
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,997
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe
Excel Formula:
=IF(F2<=K2,I2,INDEX($I$1:$I$1000,MIN(IF(F2<$K$2:$K$1000,ROW($K$2:$K$1000),""))))
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,498
These lookup functions can be tricky, how about:

Book1
FGHIJK
1# in lineAvailabilityDateTotal Build
215/3/20225/3/2022144
325/3/20225/4/2022184
41505/4/20225/18/2022232
52325/18/20225/19/2022280
63005/23/20225/23/2022328
74005/25/20225/24/2022376
84235/25/20225/25/2022424
94245/25/20226/6/2022472
104256/6/20226/7/2022520
115006/7/20226/21/2022568
Sheet3
Cell Formulas
RangeFormula
G2:G11G2=MINIFS($I$2:$I$11,$K$2:$K$11,">="&F2)
 

Forum statistics

Threads
1,176,636
Messages
5,904,175
Members
435,075
Latest member
scotte19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top