Possible Look Up and Match With Condition

[Celticshadow]

Hi Forum

I have two sheets one named Dist and the other Dist Res. What I would like help with is having a formula that will extract the date from col A in the Dist sheet which has a number equal to or lower than 2 (in col F) and is the latest occurrence and matches the horse in Dist Res sheet and places the date in B2 (If a horse does not have a number lower than 2 then return 0). I have attached an example below in the hope that it will make my query a little clearer.

So the Horse A Boy Named Ivy most recently ran a race with a dist btn less than or equal to 2 on the 15 September 2022 thus that date then needs copied and put into the Dist Res sheet next to said horse name in cell B2.

So the Horse A Case of You most recently ran a race with a dist btn less than or equal to 2 on the 11 February 2022 thus that date then needs copied and put into the Dist Res sheet next to said horse name in cell B2.

So the Horse A Distant Place has not most recently ran a race with a dist btn less than or equal to 2 thus that date does not exist thus a number zero nedds to be entered into the Dist Res sheet next to said horse name in cell B2.


Many thanks in advance.

Regards

 

[Celticshadow]

Hi Forum

I have attached a google docs link with an explanation in the Dist Res sheet for reference which may help clarify my query a little better I hope. I look forward to hearing from you. Many thanks.

Regards

Untitled spreadsheet - Google Drive

docs.google.com

 

[etaf]

it maybe my settings, but all i can see is an image - usually i get a different view and can download to excel

 

[Celticshadow]

it maybe my settings, but all i can see is an image - usually i get a different view and can download to excel

Hi etaf

Many thanks for replying it is much appreciated as I am completely stuck with this problem and would dearly wish it to be solved. I am unsure as to why the file can not be seen in google docs however I have loaded up a copy to dropbox and have attached a link below in the hope that this link will be more user friendly. Many thanks in advance.

Regards

Mr Excel Possible Look up.xlsm

Shared with Dropbox

www.dropbox.com

PS Sincere apologies my fault entirely it was settings and here is the google docs link I hope.

Untitled spreadsheet

Dist racedate,racetime,track,horse_name,comptime_numeric,TotalDstBt,MedianOR 26 March 2022,14:25,Doncaster,A Boy Named Ivy,98.4,1.75,83 23 April 2022,14:20,Ripon,A Boy Named Ivy,100.52,1.25,85.5 08 May 2022,16:48,Hamilton,A Boy Named Ivy,105.04,4.4,80 22 May 2022,17:05,York,A Boy Named Ivy,96.45...

docs.google.com

 

[etaf]

yes, that worked
as you have 365 version of excel
I think this will work
=MAX(FILTER(Dist!$A$2:$A$21,(Dist!$D$2:$D$21=A2)*(Dist!$F$2:$F$21<=2),0))

BUT because the cell will be formatted as date - then zero will display as a date 0/1/1900
working on that - to change the format - and format() did not work.....

Mr Excel Possible Look up.xlsm
	A	B	C
1	horse_name	DSDR
2	A Boy Named Ivy	Thursday, 15 September 2022	Thursday, 15 September 2022
3	A Case Of You	Friday, 11 February 2022	Friday, 11 February 2022
4	A Cheap Thrill	Friday, 31 March 2023	Friday, 31 March 2023
5	A Distant Place	0	Saturday, 0 January 1900
Dist Res

Cell Formulas
Range	Formula
C2:C5	C2	=MAX(FILTER(Dist!$A$2:$A$21,(Dist!$D$2:$D$21=A2)*(Dist!$F$2:$F$21<=2),0))

Mr Excel Possible Look up.xlsm
	A	B	C	D	E	F	G
1	racedate	racetime	track	horse_name	comptime_numeric	TotalDstBt	MedianOR
2	Saturday, 26 March 2022	14:25	Doncaster	A Boy Named Ivy	98.4	1.75	83
3	Saturday, 23 April 2022	14:20	Ripon	A Boy Named Ivy	100.52	1.25	85.5
4	Sunday, 8 May 2022	16:48	Hamilton	A Boy Named Ivy	105.04	4.40	80
5	Sunday, 22 May 2022	17:05	York	A Boy Named Ivy	96.45	6.03	83.5
6	Wednesday, 22 June 2022	14:55	Carlisle	A Boy Named Ivy	98.04	8.45	79
7	Saturday, 6 August 2022	14:52	Redcar	A Boy Named Ivy	94.36	5.00	73.5
8	Thursday, 15 September 2022	16:45	Ayr	A Boy Named Ivy	101.58	0.00	70.5
9	Saturday, 1 October 2022	15:57	Redcar	A Boy Named Ivy	97.38	3.75	77.5
10	Friday, 11 February 2022	19:30	Dundalk	A Case Of You	72.46	1.25	101
11	Saturday, 21 May 2022	14:45	Curragh	A Case Of You	72.12	4.25	109
12	Saturday, 18 June 2022	16:20	Ascot	A Case Of You	72.17	6.36	112
13	Sunday, 11 September 2022	14:25	Curragh	A Case Of You	62.51	7.55	107
14	Saturday, 15 January 2022	11:45	Fairyhouse	A Cheap Thrill	257	33.00	0
15	Thursday, 10 February 2022	14:20	Thurles	A Cheap Thrill	256.01	19.50	92
16	Friday, 30 September 2022	17:02	Gowran Park	A Cheap Thrill	314.3	0.00	102
17	Sunday, 11 December 2022	15:20	Cork	A Cheap Thrill	302.7	39.65	89.5
18	Wednesday, 28 December 2022	12:35	Leopardstown	A Cheap Thrill	320.9	30.75	109
19	Friday, 31 March 2023	18:40	Wexford	A Cheap Thrill	453.9	1.80	86
20	Wednesday, 23 February 2022	15:00	Ludlow	A Distant Place	232.5	5.00	118
21	Wednesday, 30 March 2022	15:30	Market Rasen	A Distant Place	308.87	68.25	125
Dist

added to dropbox, will only be available for a few days

Mr Excel Possible Look up -ETAF.xlsm

Shared with Dropbox

www.dropbox.com

meanwhile i'll see how to get a date formatted cell to display a zero

 

[etaf]

ok, that was interesting -
found in the forum here

How to make Date cell show = 0, not 1/0/1900?

I'm trying to make a custom spreadsheet from existing data to show due dates but the issue is if there is currently no due date the cell has a zero value and shows 1/0/1900. For simplicity A1 would be where I want to show due dates and B1 is the existing data (A1=B1). So if B1 is blank I get...

www.mrexcel.com

change the format of the cells to
custom
[>0]dddd, dd mmmm yyyy;General

Mr Excel Possible Look up -ETAF.xlsm
	A	B	C	D
1	horse_name	DSDR
2	A Boy Named Ivy	Thursday, 15 September 2022	Thursday, 15 September 2022
3	A Case Of You	Friday, 11 February 2022	Friday, 11 February 2022
4	A Cheap Thrill	Friday, 31 March 2023	Friday, 31 March 2023
5	A Distant Place	0	0
Dist Res

Cell Formulas
Range	Formula
C2:C5	C2	=MAX(FILTER(Dist!$A$2:$A$21,(Dist!$D$2:$D$21=A2)*(Dist!$F$2:$F$21<=2),TEXT(0,"0")))

 

[Celticshadow]

Dear etaf

I am ever so grateful that you have been kind enough to take time out of your day to pass on your knowledge and skillset to provide an answer to my query and queries of others. This forum and its members such as yourself never cease to amaze me, as you also have to try and decipher what luddites such as I are trying to convey before attempting to provide an answer. As per all my requests your solution works an absolute treat and I am truly thankful for your much needed help. Thank you ever so much.

Kindest Regards

 

[etaf]

thanks for those kind remarks
you are very welcome