Matching text in 3 cells and return number in corresponding cell

Goddess

Board Regular
Joined
Dec 3, 2015
Messages
118
Office Version
  1. 2021
Platform
  1. Windows
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
ABCDEFGHI
1dateCountryFruitQuantityGroupNo
223/4/2021JapanPeach20Japan-|-2021-|-Peach -|20
315/1/2021JapanPeach20Japan-|-2022-|-Melon -|40
46/7/2022JapanMelon40Korea-|-2021-|-Melon -|15
56/8/2021KoreaMelon15
69/7/2021KoreaMelom15
7
Sheet1
Cell Formulas
RangeFormula
D2D2=H2
D3:D5D3=H2
D6D6=H4


Appreciate any help on this! Thanks!
 
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
ABCDEFGHIJ
1dateCountryFruitQuantityGroupNo
24/23/21JapanPeach2020Japan-|-2021-|-Peach -|20Japan-|-2021-|-Peach-|FALSE
31/15/21JapanPeach2020Japan-|-2022-|-Melon -|40Japan-|-2021-|-Peach-|
47/6/22JapanMelon4040Korea-|-2021-|-Melon -|15Japan-|-2022-|-Melon-|
58/6/21KoreaMelon1515Korea-|-2021-|-Melon-|
67/9/21KoreaMelom15#CALC!Korea-|-2021-|-Melom-|
7-|-1900-|--|
8
Sheet1
Cell Formulas
RangeFormula
D2D2=H2
E2:E6E2=FILTER($H$2:$H$4,$G$2:$G$4=B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|")
D3:D5D3=H2
D6D6=H4
J2J2=G2=I2
I2:I7I2=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
ABCDEFGH
1dateCountryFruitQuantityGroupNo
24/23/21JapanPeach202020Japan-|-2021-|-Peach -|20
31/15/21JapanPeach202020Japan-|-2022-|-Melon -|40
47/6/22JapanMelon404040Korea-|-2021-|-Melon -|15
58/6/21KoreaMelon151515
67/9/21KoreaMelom15#CALC!#N/A
Sheet1
Cell Formulas
RangeFormula
D2D2=H2
E2:E6E2=FILTER($H$2:$H$4,$G$2:$G$4=B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|")
F2:F6F2=INDEX($H$2:$H$4,MATCH(B2&"-|-"&YEAR(A2)&"-|-"&C2&" -|",$G$2:$G$4,0))
D3:D5D3=H2
D6D6=H4
 
Upvote 0
.. or spill all the results from one formula:

25 03 05.xlsm
ABCDEFGH
1dateCountryFruitQuantityGroupNo
223/04/2021JapanPeach20Japan-|-2021-|-Peach -|20
315/01/2021JapanPeach20Japan-|-2022-|-Melon -|40
46/07/2022JapanMelon40Korea-|-2021-|-Melon -|15
56/08/2021KoreaMelon15
69/07/2021KoreaMelom#N/A
Lookup
Cell Formulas
RangeFormula
D2:D6D2=VLOOKUP(B2:B6&"-|-"&YEAR(A2:A6)&"-|-"&C2:C6&" -|",G2:H4,2,0)
Dynamic array formulas.
 
Upvote 0
Solution
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top