Matching dates along with a lookup

markdoc5

New Member
Joined
Jan 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMN
293Voicemail Leftmy name161.642857114226325/01/2021My NameCXP - My Name0.63#NAME?NONE#NAME?#NAME?#NAME?
391Call Back Requestedmy name939.52187925/01/2021My NameCXP - My Name0.52#NAME?NONE#NAME?#NAME?#NAME?
448Completed Callmy name339.7528951325/01/2021My NameCXP - My Name2.64#NAME?NONE#NAME?#NAME?#NAME?
table3
Cell Formulas
RangeFormula
G293,G448,G391G293=IFERROR(VLOOKUP(B:B,'Team List'!A:B,2,FALSE),"NONE")
H293,H448,H391H293=IFERROR(VLOOKUP(B:B,'Team List'!D:E,2,FALSE),"NONE")
I293,I448,I391I293=E293/3600
J293,J448,J391J293=XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!AI:AI,0)
K293,K448,K391K293=IFERROR(I293/J293,"NONE")
L293,L448,L391L293=_xlfn.XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!AF:AF,0)
M293,M448,M391M293=_xlfn.XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!H:H,0)
N293,N448,N391N293=_xlfn.XLOOKUP(H:H,'Call Data'!B:B,'Call Data'!AH:AH,0,0)
E293,E448,E391E293=C293*D293
Named Ranges
NameRefers ToCells
'Team List'!_FilterDatabase='Team List'!$A$1:$E$422G293, G391, G448


here is sheet 2
TESTING.xlsx
ABAFAGAHAI
1124/01/2021My Name7.57638.327.21
Call Data
Cell Formulas
RangeFormula
AF11AF11=(K11-T11-U11-V11)/3600
AG11AG11=H11
AH11AH11=AG11/AF11
AI11AI11=(Q11+R11+S11+L11)/3600

TESTING.xlsx
ABAFAGAHAI
12725/01/2021My Name7.54547.166.50
Call Data
Cell Formulas
RangeFormula
AF127AF127=(K127-T127-U127-V127)/3600
AG127AG127=H127
AH127AH127=AG127/AF127
AI127AI127=(Q127+R127+S127+L127)/3600
 

markdoc5

New Member
Joined
Jan 27, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,367
Office Version
  1. 365
Platform
  1. Windows
Bit hard to tell from that sample data. Would something like this (for row 293 for example) do what you want?

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

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,329
Members
415,892
Latest member
ChloeLM

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