Look up value and Return value of most recent date

[YS4U]

SKU	STORE A	A DATE	STORE B	B DATE

71011	1.87	2/21/21	1.69	10/7/21
71115	1.07	3/22/22	NA	X
FPF3	NA	X	1.78	3/22/22
650876	6.34	9/21/21	6.08	10/8/21
503EZ	NA	X	9.00	3/22/22
72014	3.60	2/22/22	NA	X
98823	NA	X	5.05	2/21/22

PROBLEM: look up SKU and return most recent price paid.
Hello! I'm trying to get a formula that will look up SKU on SHEET1 and grab the most recent price paid. I'm using 365 and of course am loving the XLOOKUP, but maybe this is more of an index/match thing I figured it out for an in row if(or statement used in another formula that had to deal with the Xs and NAs but I can't seem to figure out how to find the SKU then do a conditional statement to return the date. I have 365. I tried taking a sample using the XL2BB, but my spreadsheet wasn't having it and after 15 minutes of it thinking, I abandoned so hopefully the table here will be enough info. I'm sure it's simple for the pros

 

[YS4U]

I don't know why my table headers and columns are not aligned; I created a table and this is what it did. For simplicity just call the data row headers A, B, C, D, E and ignore SKU, STORE A, etc. I can't figure out how to add something to the beginning find the SKU then apply this formula in the row of the found SKU from another sheet without the use of a helper column.

=IF(E2="NA",B2,IF(OR(C2="NA",C2<E2), D2,B2))

 

[YS4U]

I'm trying to make it easy to understand, but those aren't my headers and the formula that I'm working with to try to apply to finding SKU (which is in column H for one store and I for the other) isn't transferring correctly. I wish the XL2BB thing worked

So on SHEET2 a SKU is entered that can be found in H or I; Store A(H) has more products than Store B(I). So the formula would
1. Find the SKU entered on SHEET2 in SHEET1 Columns H and I
2. From the row where the SKU is found, spit out the price last paid

H = Store A SKU
I = Store B SKU
J = Store A Price
K = Store A Date Purchased
M = Store B Price
N = Store B Date Purchased
NO = Not available at that store/No data/never purchased from that store
'--- = No Data

on SHEET1 I have a helper column now that works =IF(N2="---",J2,IF(OR(K2="---",K2<N2), M2,J2)) I'd like to put one formula on Sheet2 that can handle this but I can't figure out the part where it finds the SKU in H and/or I
Let me try adding a table with more accurate headers

H	I	J	K	M	N
71011	330CV	1.87	2/21/21	1.69	10/21/21
71115	14236	1.03	3/22/22	2.40	---
NO	FPF3	NO	---	1.78	3/22/22
650876	8830CV	6.34	9/21/21	6.08	10/8/21
NO	503EZ	NO	---	9.00	3/22/22
72014	NO	3.60	2/22/22	NO	---
NO	98823	NO	---	5.05	2/21/22

 

[jtakw]

Hi,

A suggestion, how about just Nest a couple of VLOOKUP, something like:

IFERROR(VLOOKUP(1st range using H),VLOOKUP(2nd range using I))