HLOOKUP and MATCH

[Jar888]

Just wondering what's going on, because my formula is giving me two different results depending on where it's located. I'll add some cells and some screen shots. Just not sure why it's working for some cells and not for others. I assumed HLOOKUP as a TRUE position gave me the last match in a row. Please see below.

First where it's wrong;

Cheat Sheet.xlsx
	AO	AP	AQ	AR	AS	AT
88	AL	AL	AL	AL	16/06/2022
89	AL	AL	AL	D	16/06/2022
Roster Dump

Cell Formulas
Range	Formula
AO88:AR89	AO88	=IFERROR(INDEX('F:\Data\2579\13 Progress Reporting Programming\05 Wkly Rpts\Reference Files\[EMPLOYEE ROSTER.xlsx]ROSTER'!$N$5:$AWG$250,MATCH($A88,'F:\Data\2579\13 Progress Reporting Programming\05 Wkly Rpts\Reference Files\[EMPLOYEE ROSTER.xlsx]ROSTER'!$E$5:$E$250,0),MATCH(AO$2,'F:\Data\2579\13 Progress Reporting Programming\05 Wkly Rpts\Reference Files\[EMPLOYEE ROSTER.xlsx]ROSTER'!$3:$3,0)),"")
AS88:AS89	AS88	=IFERROR(IF(B88="","",INDEX($C$2:$AR$2,MATCH(HLOOKUP("AL",C88:AR88,1,FALSE),C88:AR88,0))),"")
AT88:AT89	AT88	=IFERROR(IF(B88="","",INDEX($C$2:$AR$2,MATCH(HLOOKUP("AL",C88:AR88,1,TRUE),C88:AR88))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AR300	Expression	=A1="AL"	text	NO

As you can see, it's not picking up the last date for whatever reason. But later down the sheet I get;

From;

Cheat Sheet.xlsx
	AS	AT
142	13/05/2022	18/05/2022
143	2/06/2022	2/06/2022
Roster Dump

Cell Formulas
Range	Formula
AS142:AS143	AS142	=IFERROR(IF(B142="","",INDEX($C$2:$AR$2,MATCH(HLOOKUP("AL",C142:AR142,1,FALSE),C142:AR142,0))),"")
AT142:AT143	AT142	=IFERROR(IF(B142="","",INDEX($C$2:$AR$2,MATCH(HLOOKUP("AL",C142:AR142,1,TRUE),C142:AR142))),"")

 

[Alex Blakenburg]

HLOOKUP works the same as VLOOKUP, it gives you the first occurence.
Try LOOKUP

Book1
	AL	AM	AN	AO	AP
87	16/06/2022	17/06/2022	18/06/2022	19/06/2022
88	AL	AL	AL	AL	19/06/2022
89	AL	AL	AL	D	18/06/2022
Sheet1

Cell Formulas
Range	Formula
AP88:AP89	AP88	=LOOKUP("AL",$AL88:$AO88,$AL$87:$AO$87)

 

[Jar888]

I just get #N/A error when I try that?

Cheat Sheet.xlsx
	AU
88	#N/A
Roster Dump

Cell Formulas
Range	Formula
AU88	AU88	=LOOKUP("AL",$C88:$AR88,$C$2:$AR$2)

 

[Peter_SSs]

Edit: Ah, I see that while composing my post one of the issues mentioned may have arisen for the OP.

Try LOOKUP

Just a slight word of warning if using LOOKUP - some strange results may arise if "AL" does not occur in the row. See highlighted cells below - especially AP91
Could be modified as in column AQ or an alternative in column AR

22 05 11.xlsm
	AL	AM	AN	AO	AP	AQ	AR
87	16/06/2022	17/06/2022	18/06/2022	19/06/2022
88	AL	AL	AL	AL	19/06/2022	19/06/2022	19/06/2022
89	D	AL	AL	D	18/06/2022	18/06/2022	18/06/2022
90	D				#N/A
91	AB	AB	AB		18/06/2022
LOOKUP

Cell Formulas
Range	Formula
AP88:AP91	AP88	=LOOKUP("AL",$AL88:$AO88,$AL$87:$AO$87)
AQ88:AQ91	AQ88	=IF(COUNTIF(AL88:AO88,"AL"),LOOKUP("AL",$AL88:$AO88,$AL$87:$AO$87),"")
AR88:AR91	AR88	=IFERROR(AGGREGATE(14,6,AL$87:AO$87/(AL88:AO88="AL"),1),"")

 

[Jar888]

I'll have a look at the aggregate solution.

I thought about using countif, but the problem arises when AL is split across a break. Example below;

 

[Peter_SSs]

the problem arises when AL is split across a break.

We have no knowledge of how or if or why that would affect the result that you are after.

 

[Jar888]

Aggregate works pretty good. I've tested it on a few different extracts.

See below CountIF function;

Cheat Sheet.xlsx
	AN	AO	AP	AQ	AR	AS	AT
22	AL			AL		15/06/2022	#N/A
Roster Dump

Cell Formulas
Range	Formula
AP22,AR22	AP22	=IFERROR(INDEX('[EMPLOYEE ROSTER.xlsx]ROSTER'!$N$5:$AWG$250,MATCH($A22,'[EMPLOYEE ROSTER.xlsx]ROSTER'!$E$5:$E$250,0),MATCH(AP$2,'[EMPLOYEE ROSTER.xlsx]ROSTER'!$3:$3,0)),"")
AS22	AS22	=IFERROR(IF(B22="","",INDEX($C$2:$AR$2,MATCH(HLOOKUP("AL",C22:AR22,1,FALSE),C22:AR22,0))),"")
AT22	AT22	=IF(COUNTIF(AL22:AR22,"AL"),LOOKUP("AL",AL2:AR2,AL22:AR22),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:AR300	Expression	=A1="AL"	text	NO

 

[Peter_SSs]

Aggregate works pretty good. I've tested it on a few different extracts.

Good to hear.

See below CountIF function;

In that formula you have the row ranges the wrong way around in the LOOKUP function.

 

[Jar888]

Oh yep. I see that now.

Aggregate works though haha.