Lookup/Vlookup to find exact date

[UndwaterExcelWeaver]

I am using the formula below to try and extract data from another workbook. It works fine until there is a date with no associated values. In the formula, "D1" is the destination sheet's date, and column B is the date column in the source sheet. Column J is the data I want to extract. For example, when I am looking for February 14th, it correctly finds and inserts the data associated with the 14th. There is no data associated with the 15th. However, when I run the formula in the destination sheet with the 15th as the date, it continues to pull in the data from 14th because it can't find anything for the 15th. I need the destination sheet to return no value if there is no value for that date in the source sheet.

I believe I need to use Vlookup instead of lookup so it only looks for the exact date, but I cannot figure this out so it works. I am always getting errors when trying this setup. Please help.

=LOOKUP(D1,'[Source.xlsx]sheet'!$B:$B,'[Destination.xlsx]sheet'!$J:$J)

 

[UndwaterExcelWeaver]

I am using the formula below to try and extract data from another workbook. It works fine until there is a date with no associated values. In the formula, "D1" is the destination sheet's date, and column B is the date column in the source sheet. Column J is the data I want to extract. For example, when I am looking for February 14th, it correctly finds and inserts the data associated with the 14th. There is no data associated with the 15th. However, when I run the formula in the destination sheet with the 15th as the date, it continues to pull in the data from 14th because it can't find anything for the 15th. I need the destination sheet to return no value if there is no value for that date in the source sheet.

I believe I need to use Vlookup instead of lookup so it only looks for the exact date, but I cannot figure this out so it works. I am always getting errors when trying this setup. Please help.

=LOOKUP(D1,'[Source.xlsx]sheet'!$B:$B,'[Destination.xlsx]sheet'!$J:$J)

Just to be clear, the two sheets are in different workbooks

 

[awoohaw]

LOOKUP doesnt only look for exact matches.
Unsure of your version, but one of the three suggestions below should provide your answer:
VLOOKUP is susceptible to corruption by moving columns and data around.
XMATCH is in 365 and possibly later versions of Excel.
INDEX(MATCH()) and VLOOKUP are in nearly all the versions.

(Change "Not Found" in the formula to what ever suits you.)

Book1
	A	B	C	D	E	F	G	H
1
2		LOOKUP	INDEX(MATCH())	VLOOKUP	XLOOKUP
3	2024-02-12	8	8	8	8		2024-02-12	8
4	2024-02-13	10	10	10	10		2024-02-13	10
5	2024-02-14	15	15	15	15		2024-02-14	15
6	2024-02-15	15	Not Found	Not Found	Not Found		2024-02-16	17
7	2024-02-16	17	17	17	17		2024-02-17	8
8	2024-02-17	8	8	8	8		2024-02-18	7
9	2024-02-18	7	7	7	7		2024-02-21	6
10	2024-02-19	7	Not Found	Not Found	Not Found
11	2024-02-20	7	Not Found	Not Found	Not Found
12	2024-02-21	6	6	6	6
13
Sheet2

Cell Formulas
Range	Formula
B3:B12	B3	=LOOKUP(A3,$G$3:$G$9,$H$3:$H$9)
C3:C12	C3	=IFERROR(INDEX($H$3:$H$9,MATCH($A3,$G$3:$G$9,0)),"Not Found")
D3:D12	D3	=IFERROR(VLOOKUP(A3,$G$3:$H$9,2,0),"Not Found")
E3	E3	=XLOOKUP($A3,$G$3:$G$9,$H$3:$H$9,"Not Found",0,1)
E4:E12	E4	=XLOOKUP(A4,$G$3:$G$9,$H$3:$H$9,"Not Found",0,1)

 

[UndwaterExcelWeaver]

LOOKUP doesnt only look for exact matches.
Unsure of your version, but one of the three suggestions below should provide your answer:
VLOOKUP is susceptible to corruption by moving columns and data around.
XMATCH is in 365 and possibly later versions of Excel.
INDEX(MATCH()) and VLOOKUP are in nearly all the versions.

(Change "Not Found" in the formula to what ever suits you.)

Book1
	A	B	C	D	E	F	G	H
1
2		LOOKUP	INDEX(MATCH())	VLOOKUP	XLOOKUP
3	2024-02-12	8	8	8	8		2024-02-12	8
4	2024-02-13	10	10	10	10		2024-02-13	10
5	2024-02-14	15	15	15	15		2024-02-14	15
6	2024-02-15	15	Not Found	Not Found	Not Found		2024-02-16	17
7	2024-02-16	17	17	17	17		2024-02-17	8
8	2024-02-17	8	8	8	8		2024-02-18	7
9	2024-02-18	7	7	7	7		2024-02-21	6
10	2024-02-19	7	Not Found	Not Found	Not Found
11	2024-02-20	7	Not Found	Not Found	Not Found
12	2024-02-21	6	6	6	6
13
Sheet2

Cell Formulas
Range	Formula
B3:B12	B3	=LOOKUP(A3,$G$3:$G$9,$H$3:$H$9)
C3:C12	C3	=IFERROR(INDEX($H$3:$H$9,MATCH($A3,$G$3:$G$9,0)),"Not Found")
D3:D12	D3	=IFERROR(VLOOKUP(A3,$G$3:$H$9,2,0),"Not Found")
E3	E3	=XLOOKUP($A3,$G$3:$G$9,$H$3:$H$9,"Not Found",0,1)
E4:E12	E4	=XLOOKUP(A4,$G$3:$G$9,$H$3:$H$9,"Not Found",0,1)

Thank you very much. XLookup achieved what I needed.

 

[UndwaterExcelWeaver]

Thank you very much. XLookup achieved what I needed.

My next question would be, if I have more than one set of data per date, how would I alter the formula to return either a sum or an average of all rows with that date present?

 

[awoohaw]

You probably should put that in a fresh post since this is about LOOKUP, and not FILTER/SUMIF/SUMIFS/SUMPRODUCT/SUM and array functions/formulas.
I'd be happy to answer it there.

But, you do need to update your profile with your Excel version. And using the xl2bb add in (link below) to share mini worksheets is very helpful and you should use it if you can. If you cannot, then post your sample data in TABLES.

 

[UndwaterExcelWeaver]

You probably should put that in a fresh post since this is about LOOKUP, and not FILTER/SUMIF/SUMIFS/SUMPRODUCT/SUM and array functions/formulas.
I'd be happy to answer it there.

But, you do need to update your profile with your Excel version. And using the xl2bb add in (link below) to share mini worksheets is very helpful and you should use it if you can. If you cannot, then post your sample data in TABLES.

I have updated my profile and posted a new thread.