Three Way lookup

[Mark F]

Hi,

In Cell D1 in my "Shipping" sheet I have a Purchase Order Number
In Cell D2 I have the Last four digits of a UPS tracking number
UPS Tracking Numbers are 15 Characters long
In D3 I want to record the date a shipment with PO Number from D1 and Last 4 UPS digits in D2 was received.

In my "Back Order" Workbook:
All PO Numbers are recorded in Column A
Full 15 digit UPS Tracking Numbers are recorded in Column V
Date received is on Column U

=IF(D1<>"",XLOOKUP("*"&D1&"*",'[Back orders.xlsx]MAIN'!$a:$a,'[Back orders.xlsx]MAIN'!$U:$U,"no match",2),"")

The version above does partially work, in that it does find a date in Column U for a PO, but it's no good if the order wasn't received on the specified UPS shipment (in D2)
I've tried various options using MID formula to find the last 4 digits of the UPS reference in Column V, but I just cannot get there.

Any help appreciated

Thanks Mark

 

[awoohaw]

Try this:
( I'm not going to build a testing workbook, but I think you can adjust as needed).
But, have you considered using power query to query your data? You may not have to have the external workbook open to do the lookup. In this scenario I think you need to have the workbook open.
This also uses exactly 11 placeholder characters, You could also have an asterix in the front instead of 11 question marks.

Book1
	A	B	C	D	E	F	G	H	I	J
1				105657
2				2815			ZZZZZZZZZZZ2814	105657	2024-03-11
3							ZZZZZZZZZZZ4317	105658	2024-03-10
4			2024-03-11				ZZZZZZZZZZZ3919	105659	2024-03-13
5							ZZZZZZZZZZZ3237	105660	2024-03-14
6							ZZZZZZZZZZZ2815	105657	2024-03-11
7							ZZZZZZZZZZZ4673	105661	2024-03-15
8							ZZZZZZZZZZZ4285	105662	2024-03-16
9							ZZZZZZZZZZZ3885	105663	2024-03-17
10							ZZZZZZZZZZZ4516	105664	2024-03-18
11							ZZZZZZZZZZZ3058	105665	2024-03-19
12							ZZZZZZZZZZZ4740	105666	2024-03-20
13							ZZZZZZZZZZZ4190	105667	2024-03-21
Sheet1

Cell Formulas
Range	Formula
C4	C4	=XLOOKUP("???????????"&D2&D1,$G$2:$G$13&$H$2:$H$13,$I$2:$I$13,"Not Found",2,1)

 

[Mark F]

Thank you.

I should have mentioned that that the UPS tracking number in column V is usually followed by other information in the same cell. I tried this code. It does look up the date, based on the PO number, seems to ignore the tracking number. As always any help appreciated.

=XLOOKUP("*"&D2&"*"&D1,MID('[Back orders.xlsx]MAIN'!$V5:$V5000,15,4)&'[Back orders.xlsx]MAIN'!$A5:$A5000,'[Back orders.xlsx]MAIN'!$U5:$U5000,,2,1)

 

[Alex Blakenburg]

Give this a try:

=XLOOKUP(1,('[Back Orders.xlsx]MAIN'!$A:$A=D1)*(MID('[Back Orders.xlsx]MAIN'!$V:$V,11,5)=TRIM(D2)),'[Back Orders.xlsx]MAIN'!$U:$U,"")

 

[Mark F]

Give this a try:

=XLOOKUP(1,('[Back Orders.xlsx]MAIN'!$A:$A=D1)*(MID('[Back Orders.xlsx]MAIN'!$V:$V,11,5)=TRIM(D2)),'[Back Orders.xlsx]MAIN'!$U:$U,"")

Thank you. Perfect!

 

[Alex Blakenburg]

You're welcome. Glad we could help.