Highlighted
New Contributor

# 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

2 Replies
Highlighted

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

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.

Highlighted

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

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.