Forum Discussion

11392's avatar
11392
Copper Contributor
Oct 20, 2020

Excel, search data in different Sheet2 and put it back on Sheet1 based on their column

This is Sheet1

 

This is Sheet2

 

I would like to search the Fruit in Sheet2, identify their group, and put it back in Sheet1, under column B (Group)

 

Desired output

excel
Formulas and Functions

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 20, 2020

    11392 

    Let's say the data on Sheet2 are in A1:C15, with headers in A1:C1.

    In B2 on Sheet1, enter the formula:

     

    =IFERROR(INDEX(Sheet2!$A$1:$C$1,SUMPRODUCT((Sheet2!$A$2:$C$15=A2)*(COLUMN(Sheet2!$A$2:$C$2)-COLUMN(Sheet2!$A2)+1))),"-")

     

    Then fill down.

    I recommend structuring Sheet2 differently:

     

     

    You can then use a simple VLOOKUP formula:

     

    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$36,2,FALSE),"-")

     

    Adjust the range as needed, then fill down.

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Oct 20, 2020

    11392 

     

    You may use this:

     

     

    N.B. 

    • For better understanding I've entered data in one sheet but I've used the cell references reading from Sheet 2.
    • Enter this formula in cell B26:
    =IF(ISBLANK(A26),"",IFERROR(INDEX(sheet2!$D$25:$G$25,SUMPRODUCT(MAX((Sheet2!$D$26:$G$29=$A26)*(COLUMN(Sheet2!D$25:$G$29))))-COLUMN(Sheet2!$D$25)+1),"No Match"))

     

    • Adjust cell references in the formula as needed.

     

Resources