Lookup formula

[Johnboy28]

Hi - Looking for a formula that can lookup updated price list by matching Product Code. If no updated price is found then use previous sales price (19/09/2024) Thanks

Product Specification

Date	17/09/2024	18/09/2024	19/09/2024	20/09/2024
Product Code	Sales Price	Sales Price	Sales Price	Sales Price
12143	$11.40	$11.00	$11.00
12145	$9.00	$9.00	$9.00
12251	$9.20	$9.20	$9.20
12255	$9.00	$9.00	$9.00
12256	$9.15	$9.15	$9.15
14902	$15.00	$15.00	$15.00
20094	$3.00	$3.00	$3.00
20824	$7.60	$7.60	$7.60
20995	$8.60	$8.60	$8.60
21021	$9.15	$9.15	$9.15
21685	$7.00	$7.00	$7.00
22186	$3.80	$3.80	$3.80
Updated Price List
Product Code	Sales Price
12143	$5.00
12145	$0.00
12255	$9.10
20094	$3.00
20568	$9.50
20580	$9.35
20824	$8.20
75202	$7.80
20995	$8.60
21021	$9.10
90525	$6.50

 

[Peter_SSs]

So, what are your expected results for that sample data and where would those results go?

Please consider using XL2BB for providing sample data and expected results.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Johnboy28]

So, what are your expected results for that sample data and where would those results go?

Please consider using XL2BB for providing sample data and expected results.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thanks for getting back to me. The results would go under date 20/09/2024. I have entered the first 2 results.

Product Specification

Date	17/09/2024	18/09/2024	19/09/2024	20/09/2024
Product Code	Sales Price	Sales Price	Sales Price	Sales Price
12143	$11.40	$11.00	$11.00	$5.00
12145	$9.00	$9.00	$9.00	$9.00
12251	$9.20	$9.20	$9.20	Results here
12255	$9.00	$9.00	$9.00	Results here
12256	$9.15	$9.15	$9.15	Results here
14902	$15.00	$15.00	$15.00	Results here
20094	$3.00	$3.00	$3.00	Results here
20824	$7.60	$7.60	$7.60	Results here
20995	$8.60	$8.60	$8.60	Results here
21021	$9.15	$9.15	$9.15	Results here
21685	$7.00	$7.00	$7.00	Results here
22186	$3.80	$3.80	$3.80	Results here
Updated Price List
Product Code	Sales Price
12143	$5.00
12255	$9.10
20094	$3.00
20568	$9.50
20580	$9.35
20824	$8.20
75202	$7.80
20995	$8.60
21021	$9.10
90525	$6.50

 

[JEC]

=IFERROR(XLOOKUP(A3:A14,J2:J11,K2:K11),D3:D14)

Product specs (headers included) in A1:E14
Updated price list (headers included) in J1:K11

 

[Johnboy28]

=IFERROR(XLOOKUP(A3:A14,J2:J11,K2:K11),D3:D14)

Thank you. I'm getting a error message "SPILL!"

 

[pitchoute]

In cell E3 (which corresponds to the Sales Price for 20/09/2024), enter the following formula:

=IFERROR(VLOOKUP(A3, $G$3:$H$13, 2, FALSE), D3)

Copy this formula down for all your products in Column E.

 

[JEC]

Clear all results in that column first. It will generate a dynamic array so teh cells below the formula have to be empty

 

[Johnboy28]

In cell E3 (which corresponds to the Sales Price for 20/09/2024), enter the following formula:



Copy this formula down for all your products in Column E.

Perfect - That works great. Thank you