Lookup if Date is < than

[rambojr]

Hi

I have multiple worksheets by year 2020 , 2019, 2018 in my Summary worksheet I want to pull up patch numbers if the date received (Cell H)is less than a date I specif in cell C21 in the Summary worksheet and if CELL I has a value of "Y"

Example
Summary Sheet

Cell C21 = March 25, 2020

Formula should display

1234 TEST PROJECT 1 & 6522 TEST PROJECT 3

Worksheet 2020
A B C D E F G H I

1234	TEST PROJECT 1	BOB	N/A	2-Feb-20	Y
5875	TEST PROJECT 2	JOHN	N/A	6/Jun/20	Y
6522	TEST PROJECT 3	LISA	N/A	1-Jan-20	Y
5572	TEST PROJECT 3	DAVID	N/A	1-Feb-20	N

 

[alansidman]

Cannot manipulate your data as shown. Please use XL2BB to upload your sample. Cannot distinguish columns nor rows from your sample.

 

[rambojr]

Sorry I don't have access to install XL2BB, see images attached below from a sample worksheet. I also included the data for 2019 and 2018 which should be part if the query

View attachment 16311

 

[rambojr]

I tired the 3 formulas below to query the 2020 sheet and got the values I needed, the only problem if I copy it down to other cells it also displays the dates where there are No Manual updates. IF there a way to only show the patch# that is less than the date in cell C21

=IF((AND('2020'!H2<$C$21,'2020'!I2="Y")), '2020'!A2,"No Manual Updates Found")
=IF((AND('2020'!H2<$C$21,'2020'!I2="Y")), '2020'!B2,"No Manual Updates Found")
=IF((AND('2020'!H2<$C$21,'2020'!I2="Y")), '2020'!H2,"No Manual Updates Found")