Forum Discussion

Belinea2010's avatar
Belinea2010
Copper Contributor
Jun 27, 2020
Solved

Formula look at sheet 1, find duplicates in sheet 2 and copy ID from sheet 1 to each duplicate

I am using Excel 2016 and I am in over my head on this.   I am trying to write a formula to look at Sheet 1 Column B and for every duplicate in Sheet 2 Column B it will copy the unique anchor/ID Nu...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 27, 2020

    Belinea2010 

    You may enter into Sheet2!A2

    =INDEX(Sheet1!A:A,MATCH(Sheet2!$B2,Sheet1!B:B,0))

    and drag it down

DevendraJain's avatar
DevendraJain
Iron Contributor
Jun 27, 2020

Belinea2010 Hey you can use Vlookup formula

 

Try writing this in Sheet 2 Cell B1

 

=+VLOOKUP(Sheet2!A1,Sheet1!$A$1:$B$100,2,FALSE)

Belinea2010's avatar
Belinea2010
Copper Contributor
Jun 27, 2020

DevendraJain 

Hi

 

Thank you for your suggestion which I really appreciate but as it did not work I thought you might like some feedback.

 

The formula should go in Sheet 2 Cell A2 and then copied down. It needed to start on row 2 as each sheet has a header row. It goes in Column A because that is where the LookUp needs to be.

 

Just in case there was a misunderstanding on my part I tried your formula in Sheet 2 Cell A1 and then in Cell B1 but the result was the same. It returns the text of “FRUIT” in the first row and then when copied down it just produces #N/A in each row.

 

Thanks again

Resources