VLOOKUP or XLOOKUP Partial match formula help

[sbutler66]

Hi,

I'm looking to update prices for a large export of products. I need to map the pricing from one sheet containing the top level product SKUs, to the sheet prepared for import which contains longer Product variation SKUs.

For example the Product SKU in the price sheet would be 1234567, and in the upload sheet with the SKU variants, they would be 1234567RED, 1234567BLUE and so on.

I would like to map using the Product variant SKUs. However, I'm having issues getting the formula to correctly identify and match the SKUs in the arrays.

I'd like to write a formula to match for the initial partial numerical match, how would I do this? Furthermore, would you suggest VLOOKUP or XLOOKUP?

I've attached a simplified version below for illustration:

Thanks in advance!

 

[Cubist]

Are the SKU numbers always the same length?

 

[sbutler66]

Are the SKU numbers always the same length?

Yes, thankfully the initial SKU will always be 7 numerical characters

 

[awoohaw]

try this:

Book2
	A	B	C	D	E	F	G
1	PRODUCT	SKU	PRICE			ProductVariantSKU	Price
2	Product1	1093432	300			1093432RED	300
3	Product2	1093447	400			1093432BLUE	300
4	Product3	1093449	500			1093447RED	400
5						1093447BLUE	400
6						1093449RED	500
7						1093449BLUE	500
Sheet2

Cell Formulas
Range	Formula
G2:G7	G2	=XLOOKUP(1*LEFT(F2,7),$B$2:$B$4,$C$2:$C$4,"Not Found",0)

 

[Cubist]

Adapting on @awoohaw answer, you can make the formula spill by grabbing the entire range.

Book3
	A	B	C	D	E	F	G	H
1	PRODUCT	SKU	PRICE			ProductVariantSKU	Price
2	Product1	1093432	300			1093432RED	300
3	Product2	1093447	400			1093432BLUE	300
4	Product3	1093449	500			1093447RED	400
5						1093447BLUE	400
6						1093449RED	500
7						1093449BLUE	500
8
Sheet3

Cell Formulas
Range	Formula
G2:G7	G2	=XLOOKUP(1*LEFT(F2:F7,7),$B$2:$B$4,$C$2:$C$4,"Not Found",0)
Dynamic array formulas.

 

[sbutler66]

try this:

Book2
	A	B	C	D	E	F	G
1	PRODUCT	SKU	PRICE			ProductVariantSKU	Price
2	Product1	1093432	300			1093432RED	300
3	Product2	1093447	400			1093432BLUE	300
4	Product3	1093449	500			1093447RED	400
5						1093447BLUE	400
6						1093449RED	500
7						1093449BLUE	500
Sheet2

Cell Formulas
Range	Formula
G2:G7	G2	=XLOOKUP(1*LEFT(F2,7),$B$2:$B$4,$C$2:$C$4,"Not Found",0)

Wow, thank you, this works perfectly for my mini example.

To extrapolate to my larger worksheets, I would expand the ranges for column B & C?

Columns F & G from above are currently in a separate worksheet in my working files, would it be easier to copy into a single sheet to fit his formula?

 

[awoohaw]

are you asking for all three columns to get returned? Then the functions can be changed a little bit.

The other worksheet needs to be open when you do these calculations.

If you want to avoid having the worksheet open you could link your source table to the current workbook with POWER QUERY.

But, try this for a quick result, and yes you need adjust your ranges.

Book2
	A	B	C	D	E	F	G	H	I
1	PRODUCT	SKU	PRICE			ProductVariantSKU
2	Product1	1093432	300			1093432RED	Product1	1093432	300
3	Product2	1093447	400			1093432BLUE	Product1	1093432	300
4	Product3	1093449	500			1093447RED	Product2	1093447	400
5						1093447BLUE	Product2	1093447	400
6						1093449RED	Product3	1093449	500
7						1093449BLUE	Product3	1093449	500
Sheet2

Cell Formulas
Range	Formula
G2:I7	G2	=LET(Prods,$A$2:$C$4,SKUS,$B$2:$B$4,sku,1*LEFT($F2,7),FILTER(Prods,SKUS=sku,"not found"))
Dynamic array formulas.

 

[sbutler66]

are you asking for all three columns to get returned? Then the functions can be changed a little bit.

The other worksheet needs to be open when you do these calculations.

If you want to avoid having the worksheet open you could link your source table to the current workbook with POWER QUERY.

But, try this for a quick result, and yes you need adjust your ranges.

Book2
	A	B	C	D	E	F	G	H	I
1	PRODUCT	SKU	PRICE			ProductVariantSKU
2	Product1	1093432	300			1093432RED	Product1	1093432	300
3	Product2	1093447	400			1093432BLUE	Product1	1093432	300
4	Product3	1093449	500			1093447RED	Product2	1093447	400
5						1093447BLUE	Product2	1093447	400
6						1093449RED	Product3	1093449	500
7						1093449BLUE	Product3	1093449	500
Sheet2

Cell Formulas
Range	Formula
G2:I7	G2	=LET(Prods,$A$2:$C$4,SKUS,$B$2:$B$4,sku,1*LEFT($F2,7),FILTER(Prods,SKUS=sku,"not found"))
Dynamic array formulas.

Thanks for your feedback. I would just need the price returning. It would be useful to not have to create a new worksheet to combine the data, or always have the others open. How would you suggesting using the power query?

 

[awoohaw]

There many youtube channels that have lessons on it. ExcelIsFun, Mr. Excel, MyOnlineTrainingHub, and Leila Gharani are the ones I like best.
But, if you are completely new to it (It is not hard to get the basics down), look at this playlist from ExcelIsFun:

Power Query (Get & Transform) Video Playlist of Videos

Videos about the amazing Excel Power Query, which allows you at import, clean and transform data from many sources and then load it to an Excel Sheet, Power ...

youtube.com

There is a lot, so, you'll have to look for the tutorials regarding lookups.

The Power Pivot playlist could help you too:

Excel Power Pivot Playlist of Videos: Full Free Class

This is a playlist of videos of a free class that teaches everything about Excel's Power Pivot. Learn abut the Data Model, DAX Formulas, Relationships and Da...

youtube.com

 

[sbutler66]

There many youtube channels that have lessons on it. ExcelIsFun, Mr. Excel, MyOnlineTrainingHub, and Leila Gharani are the ones I like best.
But, if you completely new to it (It is not hard to get the basics down), look at this playlist from ExcelIsFun:

Power Query (Get & Transform) Video Playlist of Videos

Videos about the amazing Excel Power Query, which allows you at import, clean and transform data from many sources and then load it to an Excel Sheet, Power ...

youtube.com

There is a lot, so, you'll have to look for the tutorials regarding lookups.

The Power Pivot playlist could help you too:

Excel Power Pivot Playlist of Videos: Full Free Class

This is a playlist of videos of a free class that teaches everything about Excel's Power Pivot. Learn abut the Data Model, DAX Formulas, Relationships and Da...

youtube.com

Thanks for pointing me in the right direction!