IF formula Help

[NeoSez]

I am trying to lookup only "DELIVERED" items and have it look up based on tracking number, then return the date it was delivered from Sheet 2 Col L to Sheet 1 Col J, but ONLY if Sheet 1, Col C status says DELIVERED, otherwise leave blank not some error code like Ref# or something else.
Also, how to write it from start to last row instead of L2:L100000?

Sheet 1
Column A- Tracking #
Column C - Status
Column J - Date Delivered

Sheet 2
Col E - Tracking #
Col L - Date Delivered

IF($C5="DELIVERED",LOOKUP($A5,SHEET2!E2:E10000,Sheet2!$L2:$L10000

 

[awoohaw]

This provides what you need except for the rows requirement:

mr excel questions 22.xlsm
	A		C		E					J		L	M	N
1	I am trying to lookup only "DELIVERED" items and have it look up based on tracking number, then return the date it was delivered from Sheet 2 Col L to Sheet 1 Col J, but ONLY if Sheet 1, Col C status says DELIVERED, otherwise leave blank not some error code like Ref# or something else.
2
3	Also, how to write it from start to last row instead of L2:L100000?
4					Sheet 2
5					Col E - Tracking #							Col L - Date Delivered
6					5647896
7					65487542							2023-02-05
8
9
10
11
12	Sheet 1
13	Column A- Tracking #		Column C - Status							Column J - Date Delivered
14	5647896
15	65487542		DELIVERED							2023-02-05
16
NeoSez

Cell Formulas
Range	Formula
L6:L7	L6	=IFERROR(INDEX($J$14:$J$15,MATCH($E6&"DELIVERED",$A$14:$A$15&$C$14:$C$15,0)),"")

 

[awoohaw]

In order to really work with columns I think you need to be using 365, or put the data in sheet1 into a table.

 

[NeoSez]

Thanks awoohaw. I couldn't get it to work for me, but let me play around with it some more.

 

[awoohaw]

Thanks awoohaw. I couldn't get it to work for me, but let me play around with it some more.

I matched your column letters as you described in the write up. The only thing I did not do was us a different sheet.
paste the entirety of what I have above into a new worksheet and see if it works.