SOLVED

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

Copper Contributor

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

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.

5 Replies

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

``=FILTER(Sheet2!\$F\$2:\$F\$7,ISNUMBER(SEARCH(Sheet2!\$B\$2:\$B\$7,B2)))``
best response confirmed by AL789 (Copper Contributor)
Solution

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

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.