Matching dates along with a lookup

[markdoc5]

Hi all!

I'm having some issues getting this to work (im a newbie)

Im using a xlookup to look up someones name and return a daily stat - however the 2 sheets im using both have dates attached, is it possible for the lookup to return "the name with the stat if the date matches ? not sure how the formula would work. below is the formula im using. as the current formula only pulls data from all dates rather than changing along with the dates.

=XLOOKUP(@H:H,'CALL DATA'!B:B,'CALL DATA'AF:AF,0)

So sheet one had a date on the 6th column and sheet 2 has the date on column 1

 

[Peter_SSs]

So sheet one had a date on the 6th column and sheet 2 has the date on column 1

Your formula bears no resemblance to your description.
The formula does not mention sheet one or sheet 2. Presumably 'CALL DATA' is one of those but we have no way of guessing which one.

6th column is column F and column 1 is column A. Again your formula does not mention either of those. As I understand it, that is what your question is about, but we don't know what is in columns H or AF or B.

Finally, you say that you are using XLOOKUP, but your profile says that you have Excel 2016, which does not have the XLOOKUP function.

Could you give us a small set of sample data and expected result(s) with XL2BB so that we can actually see what your data is like, where it is and what results you want. Further, we can copy that sample data to test with.

 

[markdoc5]

Sorry, as you can tell im not the best at excel !

here is sheet1
i had to copy the workbook to allow me to upload this hence why its showing as " name?" but it does show data

TESTING.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
293	Voicemail Left	my name	161.6428571	14	2263	25/01/2021	My Name	CXP - My Name	0.63	#NAME?	NONE	#NAME?	#NAME?	#NAME?
391	Call Back Requested	my name	939.5	2	1879	25/01/2021	My Name	CXP - My Name	0.52	#NAME?	NONE	#NAME?	#NAME?	#NAME?
448	Completed Call	my name	339.75	28	9513	25/01/2021	My Name	CXP - My Name	2.64	#NAME?	NONE	#NAME?	#NAME?	#NAME?
table3

Cell Formulas
Range	Formula
G293,G448,G391	G293	=IFERROR(VLOOKUP(B:B,'Team List'!A:B,2,FALSE),"NONE")
H293,H448,H391	H293	=IFERROR(VLOOKUP(B:B,'Team List'!D:E,2,FALSE),"NONE")
I293,I448,I391	I293	=E293/3600
J293,J448,J391	J293	=XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!AI:AI,0)
K293,K448,K391	K293	=IFERROR(I293/J293,"NONE")
L293,L448,L391	L293	=_xlfn.XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!AF:AF,0)
M293,M448,M391	M293	=_xlfn.XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!H:H,0)
N293,N448,N391	N293	=_xlfn.XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!AH:AH,0,0)
E293,E448,E391	E293	=C293*D293

Named Ranges
Name	Refers To	Cells
'Team List'!_FilterDatabase	='Team List'!$A$1:$E$422	G293, G391, G448

here is sheet 2

TESTING.xlsx
	A	B																														AF	AG	AH	AI
11	24/01/2021	My Name																														7.57	63	8.32	7.21
Call Data

Cell Formulas
Range	Formula
AF11	AF11	=(K11-T11-U11-V11)/3600
AG11	AG11	=H11
AH11	AH11	=AG11/AF11
AI11	AI11	=(Q11+R11+S11+L11)/3600

TESTING.xlsx
	A	B																														AF	AG	AH	AI
127	25/01/2021	My Name																														7.54	54	7.16	6.50
Call Data

Cell Formulas
Range	Formula
AF127	AF127	=(K127-T127-U127-V127)/3600
AG127	AG127	=H127
AH127	AH127	=AG127/AF127
AI127	AI127	=(Q127+R127+S127+L127)/3600

 

[markdoc5]

Your formula bears no resemblance to your description.
The formula does not mention sheet one or sheet 2. Presumably 'CALL DATA' is one of those but we have no way of guessing which one.

6th column is column F and column 1 is column A. Again your formula does not mention either of those. As I understand it, that is what your question is about, but we don't know what is in columns H or AF or B.

Finally, you say that you are using XLOOKUP, but your profile says that you have Excel 2016, which does not have the XLOOKUP function.

Could you give us a small set of sample data and expected result(s) with XL2BB so that we can actually see what your data is like, where it is and what results you want. Further, we can copy that sample data to test with.

Any Idea? - i dont know if i just explained it horribly (more than likely)

if the name and date matches then returns the result is the simplest way i can put it lol

 

[Peter_SSs]

Bit hard to tell from that sample data. Would something like this (for row 293 for example) do what you want?

=FILTER('Call Data'!AI$1:AI1000,('Call Data'!B$1:B1000=G293)*('Call Data'!A$1:A1000=F293),"")

or perhaps

=INDEX(FILTER('Call Data'!AI$1:AI1000,('Call Data'!B$1:B1000=G293)*('Call Data'!A$1:A1000=F293),""),1)