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

[11392]

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]

@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.

[#3]

@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.