Formula Help: Bring Stock #s from One Worksheet to Another

[AL789]

Please see the attached worksheets. I'd like to bring the stock #s from WORKSHEET 2 to WORKSHEET 1 using a formula. The #s in red on worksheet 1 are the results I'm looking for.

I tried a vlookup, but since some of the item #s don't exactly match (i.e. some coded numbers such as EN3061416 in WORKSHEET 1 are listed as 3061416 in WORKSHEET 2), this didn't work. But these item #s refer to the same thing and I'd like the formula to treat them the same.

Thanks.

[#2]

@AL789 

=FILTER(Sheet2!$F$2:$F$7,ISNUMBER(SEARCH(Sheet2!$B$2:$B$7,B2)))

[#3 · Accepted solution]

@AL789 

Perhaps

=IFERROR(VLOOKUP(B2,'[WORKSHEET 2 NEW.xlsx]Sheet1'!$B$2:$F$7,5,FALSE),INDEX('[WORKSHEET 2 NEW.xlsx]Sheet1'!$F$2:$F$7,MATCH(TRUE,ISNUMBER(SEARCH('[WORKSHEET 2 NEW.xlsx]Sheet1'!$B$2:$B$7,B2)),0)))

This formula first tries to find an exact match. If that is not found, it tries a partial match.

Please test carefully.

[#4]

When item numbers between worksheets don't perfectly match, consider using the INDEX and MATCH formula combination. In WORKSHEET 1, place the formula =INDEX('WORKSHEET 2'!B:B, MATCH("*" & A2 & "*", 'WORKSHEET 2'!A:A, 0)) in cell B2. This formula, incorporating wildcards, helps locate partial matches, accommodating variations in the item numbers. Apply the formula down the column for consistent outcomes. For better accuracy, it's advisable to preprocess both datasets to ensure uniform formatting. This approach addresses the issue of related item numbers appearing differently and helps achieve alignment between the worksheets.

[#5]

I can't place the formula in cell B2, as this is where my data is (for item #). I would need a formula to put in the C column of worksheet 1 to pull the quantity #s. Could you advise?

[#6]

This worked. Thank you.