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

What is the best way to do this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C
1	A	B	C
2	aaa	bbb	Grapes
3	aaa		ccc
4	Pears	bbb	ccc
5		bbb	ccc
6		Mangos
7
Sheet2

+Fluff v2.xlsm
	A	B
1	Fruit	Group
2	Grapes	C
3	Apples	-
4	Pears	A
5	Mangos	B
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX(Sheet2!$A$1:$C$1,AGGREGATE(15,6,(COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1)/(Sheet2!$A$2:$C$10=A2),1)),"-")

 

[11392]

Thanks, it works on sample data.

Btw, I've question about this part.
AGGREGATE(15,6,(COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1)/(Sheet2!$A$2:$C$10=A2),1)

What is the meaning of 1 in AGGREGATE function?

AGGREGATE function - Microsoft Support

The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

support.microsoft.com

Based on link above, I know that:

AGGREGATE(function_num, options, array, [k])

15 is a function_num for function "SMALL"

6 is an Option to "Ignore error values"

(COLUMN(Sheet2!$A$1:$C$1)-COLUMN(Sheet2!$A$1)+1)/(Sheet2!$A$2:$C$10=A2)

is the "array"

and

1 is for [k].

What is the meaning of "1 is for [k]"?

 

[Fluff]

It means the 1st smallest value