Forum Discussion

AL789's avatar
AL789
Copper Contributor
Aug 28, 2023

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.

Excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Aug 28, 2023

    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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 28, 2023

    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.

  • kashif2005's avatar
    kashif2005
    Copper Contributor
    Aug 28, 2023
    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.
    • AL789's avatar
      AL789
      Copper Contributor
      Aug 28, 2023
      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?

Share