Matching text in 3 cells and return number in corresponding cell

[Goddess]

Hi,

I have a cell with 3 criteria, column G (I have no idea why it's written the way it is) and a corresponding number next to it. In cell A to C, I have the description for the 3 criteria. I need the corresponding number in column D(highlighted in yellow):

Book1
	A	B	C	D	E	F	G	H	I
1	date	Country	Fruit	Quantity			Group	No
2	23/4/2021	Japan	Peach	20			Japan-|-2021-|-Peach -|	20
3	15/1/2021	Japan	Peach	20			Japan-|-2022-|-Melon -|	40
4	6/7/2022	Japan	Melon	40			Korea-|-2021-|-Melon -|	15
5	6/8/2021	Korea	Melon	15
6	9/7/2021	Korea	Melom	15
7
Sheet1

Cell Formulas
Range	Formula
D2	D2	=H2
D3:D5	D3	=H2
D6	D6	=H4

Appreciate any help on this! Thanks!

 

[etaf]

how about
=FILTER($H$2:$H$4,$G$2:$G$4=B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|")

should have filter 9n 2021 version

the last entry as melom not Melon

see column E

Book1
	A	B	C	D	E	F	G	H	I	J
1	date	Country	Fruit	Quantity			Group	No
2	4/23/21	Japan	Peach	20	20		Japan-|-2021-|-Peach -|	20	Japan-|-2021-|-Peach-|	FALSE
3	1/15/21	Japan	Peach	20	20		Japan-|-2022-|-Melon -|	40	Japan-|-2021-|-Peach-|
4	7/6/22	Japan	Melon	40	40		Korea-|-2021-|-Melon -|	15	Japan-|-2022-|-Melon-|
5	8/6/21	Korea	Melon	15	15				Korea-|-2021-|-Melon-|
6	7/9/21	Korea	Melom	15	#CALC!				Korea-|-2021-|-Melom-|
7									-|-1900-|--|
8
Sheet1

Cell Formulas
Range	Formula
D2	D2	=H2
E2:E6	E2	=FILTER($H$2:$H$4,$G$2:$G$4=B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|")
D3:D5	D3	=H2
D6	D6	=H4
J2	J2	=G2=I2
I2:I7	I2	=B2&"-|-"&YEAR(A2)&"-|-"&C2&"-|"

otherwise an index/match
=INDEX($H$2:$H$4,MATCH(B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|",$G$2:$G$4,0))

see F

Book1
	A	B	C	D	E	F	G	H
1	date	Country	Fruit	Quantity			Group	No
2	4/23/21	Japan	Peach	20	20	20	Japan-|-2021-|-Peach -|	20
3	1/15/21	Japan	Peach	20	20	20	Japan-|-2022-|-Melon -|	40
4	7/6/22	Japan	Melon	40	40	40	Korea-|-2021-|-Melon -|	15
5	8/6/21	Korea	Melon	15	15	15
6	7/9/21	Korea	Melom	15	#CALC!	#N/A
Sheet1

Cell Formulas
Range	Formula
D2	D2	=H2
E2:E6	E2	=FILTER($H$2:$H$4,$G$2:$G$4=B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|")
F2:F6	F2	=INDEX($H$2:$H$4,MATCH(B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|",$G$2:$G$4,0))
D3:D5	D3	=H2
D6	D6	=H4

 

[Peter_SSs]

.. or spill all the results from one formula:

25 03 05.xlsm
	A	B	C	D	E	F	G	H
1	date	Country	Fruit	Quantity			Group	No
2	23/04/2021	Japan	Peach	20			Japan-|-2021-|-Peach -|	20
3	15/01/2021	Japan	Peach	20			Japan-|-2022-|-Melon -|	40
4	6/07/2022	Japan	Melon	40			Korea-|-2021-|-Melon -|	15
5	6/08/2021	Korea	Melon	15
6	9/07/2021	Korea	Melom	#N/A
Lookup

Cell Formulas
Range	Formula
D2:D6	D2	=VLOOKUP(B2:B6&"-|-"&YEAR(A2:A6)&"-|-"&C2:C6&" -|",G2:H4,2,0)
Dynamic array formulas.

 

[Goddess]

Thanks both for the help! I was wondering why all the formula did not work, and then managed to figure it out today. There were some spaces in between some of the text! Managed to solve it today.

 

[etaf]

you are welcome