Find Closest Match in a Table, when a Row has Evaluated TRUE

jase71ds

Board Regular
Joined
Oct 23, 2006
Messages
108
Office Version
  1. 365
Platform
  1. Windows
Apparently I'm an idiot... :(
This has me stumped. I've tried SUMPRODUCT, various array formula (Ctrl+Shift+Enter), non-array formula, VLOOKUP, XLOOKUP, INDEX/MATCH)
At this point I've developed a Glazed-Over mental block. So now I'm reaching out to the sane Excel users.
I think the uploaded image thoroughly explains what I'm trying to achieve.

Thanks for any help offered. I know everyone's time is valuable and so I appreciate any charity on this matter.
Jase.
 

Attachments

  • ExcelHelp.png
    ExcelHelp.png
    34.8 KB · Views: 3

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also what version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
Assuming you're using a table, and that the dates are in increasing order by row, as in the example:

Book1 (version 1).xlsb
ABCDEFG
12/15/2021
2
3INDEXVALUE1VALUE2VALUE3VALUE4VALUE5Formula
4FALSE3/8/20214/2/20219/1/202110/5/20216/11/2022 
5TRUE1/1/20211/8/20211/24/20212/16/20213/1/20211/24/2021
6FALSE4/6/20214/10/20215/8/20215/9/20215/10/2021 
7FALSE8/3/202112/3/20211/3/20216/2/20227/4/2021 
Sheet5
Cell Formulas
RangeFormula
G4:G7G4=IF([@INDEX],LOOKUP($A$1,[@VALUE1]:[@VALUE5]),"")
 

jase71ds

Board Regular
Joined
Oct 23, 2006
Messages
108
Office Version
  1. 365
Platform
  1. Windows
Apparently I'm an idiot... :(
This has me stumped. I've tried SUMPRODUCT, various array formula (Ctrl+Shift+Enter), non-array formula, VLOOKUP, XLOOKUP, INDEX/MATCH)
At this point I've developed a Glazed-Over mental block. So now I'm reaching out to the sane Excel users.
I think the uploaded image thoroughly explains what I'm trying to achieve.

Thanks for any help offered. I know everyone's time is valuable and so I appreciate any charity on this matter.
Jase.
UPDATE: as per Fluff's recommendation, I installed XL2BB. However I was unable to edit my original post because "time had expired" so I'm replying with the Excel info.
Jase.
-----

Book1
ABCDEFGHI
12/15/2021
2
3INDEXVALUE1VALUE2VALUE3VALUE4VALUE5
4FALSE3/8/20214/2/20219/1/202110/5/20216/11/2022
5TRUE1/1/20211/8/20211/24/20212/16/20213/1/2021
6FALSE4/6/20214/10/20215/8/20215/9/20215/10/2021
7FALSE8/3/202112/3/20211/3/20226/2/20227/4/2022
8
9
10When a row on the INDEX column has evaluated TRUE
11Then take $A$1 and look through that Row, considering Columns VALUE1:VALUE5
12and return the Exact-Match-or-Next-Smaller value.
13
14In this example, the returned value would be 1/24/2021
15
Sheet1
 

jase71ds

Board Regular
Joined
Oct 23, 2006
Messages
108
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks, Eric. But this won't work in my situation.
I should have been more clear. I need the result returned in, say, B1.
I can't just add another column to the table.
I need the formula to look through the table, find what has evaluated to TRUE in the INDEX (MY_INDEX) column, and return the result.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
Try this in B1:

Excel Formula:
=LOOKUP(A1,INDEX(Table2[[VALUE1]:[VALUE5]],MATCH(TRUE,Table2[INDEX],0),0))
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Another option, assuming only one row can have TRUE
+Fluff v2.xlsm
ABCDEF
115/02/202124/01/2021
2
3INDEXVALUE1VALUE2VALUE3VALUE4VALUE5
4FALSE08/03/202102/04/202101/09/202105/10/202111/06/2022
5TRUE01/01/202108/01/202124/01/202116/02/202101/03/2021
6FALSE06/04/202110/04/202108/05/202109/05/202110/05/2021
7FALSE03/08/202103/12/202103/01/202202/06/202204/07/2022
Master
Cell Formulas
RangeFormula
B1B1=XLOOKUP(A1,FILTER(B4:F7,A4:A7),FILTER(B4:F7,A4:A7),,-1)
 

jase71ds

Board Regular
Joined
Oct 23, 2006
Messages
108
Office Version
  1. 365
Platform
  1. Windows
Try this in B1:

Excel Formula:
=LOOKUP(A1,INDEX(Table2[[VALUE1]:[VALUE5]],MATCH(TRUE,Table2[INDEX],0),0))
Eric,
I marked as solution. Many thanks! Your solution gets me back on the road and out of the ditch!!!
Jase.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,015
Messages
5,599,351
Members
414,306
Latest member
Dennis_vdw

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