Multiple MATCH

[Forestq]

Hi,

how can I add second MATCH into below function:

OFFSET(Sheet2!$BS$1,MATCH(Sheet1!$H$9,Sheet2!$BR$2:$BR$402,0),0,COUNTIF(Sheet2!$BR$2:$BR$402,Sheet1!$H$9),1))

example: If in one field I select Austria, and in second field I select Wien, I want to have in third filed ALL STREAT for Wien.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Street[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]Wien[/TD]
[TD]Bohemo[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]Wien[/TD]
[TD]Thenok[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]Wien[/TD]
[TD]Filator[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]Lienz[/TD]
[TD]Abud[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]Lienz[/TD]
[TD]Oksor[/TD]
[/TR]
</tbody>[/TABLE]

 

[J.Ty.]

I am not so sure what you really need.
What comes to my mind is that you probably already have what you need. The reason is that typically name of the city determines the name of the country uniquely. So if the user chooses "Austria, Wien" you may simply process only Wien, because there is no other Wien in Europe.
So if you check and verify, that you never have the same town name in two different countries, the solution is to forget about the country.

Pozdrowienia,

J.Ty.

 

[Forestq]

Hi J.Ty.,

maybe my example was wrong. Here is other:[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Year[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]1999[/TD]
[TD]A1[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]2000[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]2000[/TD]
[TD]AG[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]2001[/TD]
[TD]ZZ[/TD]
[/TR]
[TR]
[TD]Austria[/TD]
[TD]2002[/TD]
[TD]ZH[/TD]
[/TR]
[TR]
[TD]Poland[/TD]
[TD]1998[/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD]Poland[/TD]
[TD]2000[/TD]
[TD]AZ[/TD]
[/TR]
[TR]
[TD]Poland[/TD]
[TD]2000[/TD]
[TD]AF[/TD]
[/TR]
[TR]
[TD]Poland[/TD]
[TD]2001[/TD]
[TD]KL[/TD]
[/TR]
</tbody>[/TABLE]

Now, when I select Austria, then in first field I have list with Year. When I select year = 2000 I should have list:
AB, AG (for Austria).

Note, that "2000 Year" can occur couple times (for other country).

 

[J.Ty.]

Hi,

Do you mean this: http://www.mimuw.edu.pl/~jty/MrExcel/countries-towns.xlsx
but extended by one more level of choice?

J.Ty.

 

[Forestq]

yes, but I need one more level.

 

[Aladin Akyurek]

Hi J.Ty.,

maybe my example was wrong. Here is other:

[...]

Now, when I select Austria, then in first field I have list with Year. When I select year = 2000 I should have list:
AB, AG (for Austria).

Note, that "2000 Year" can occur couple times (for other country).

Not sure about the purpose. Taking it up as creating a sublist on demand...

[TABLE="width: 286"]
<COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3043" width=86><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3697" width=104><TBODY>[TR]
[TD="class: xl64, width: 86, bgcolor: white"]Country[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]Year[/TD]
[TD="class: xl64, width: 64, bgcolor: white"]Code[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 104, bgcolor: white"]Austria[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Austria[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]1999[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]A1[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 104, bgcolor: white"]2000[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Austria[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2000[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AB[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]CODES[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Austria[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2000[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AG[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 104, bgcolor: white"]AB[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Austria[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2001[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]ZZ[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 104, bgcolor: white"]AG[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Austria[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2002[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]ZH[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Poland[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]1998[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]A4[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Poland[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2000[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AZ[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Poland[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2000[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]AF[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 86, bgcolor: white"]Poland[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2001[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]KL[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

E4, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$10,SMALL(IF($A$2:$A$10=E$1,IF($B$2:$B$10=E$2,
  ROW($C$2:$C$10)-ROW($C$2)+1)),ROWS($C$2:C2))),"")

 

[Forestq]

I need to put it into validation list, so can I just copy?

 

[Aladin Akyurek]

I need to put it into validation list, so can I just copy?

No. You need a different route. J.Ty. appears to have sensed your purpose, so stay with him.

 

[J.Ty.]

Hi,

I have made your example data into a working spreadsheet. Please have a look and test it. At the moment data validation covers the first 10 rows in the first tab. The second tab contains your data converted into a form which is necessary for the validation to work, and a manual how to prepare it yourself.
Get the spreadsheet from here: http://www.mimuw.edu.pl/~jty/MrExcel/countries-years-codes.xlsx

Pozdrowienia,
J.Ty.

 

[Forestq]

what is mean A4, B4?

=OFFSET(Start,COUNTIF(ColD,"<"&A4)+COUNTIFS(ColD,A4,ColE,"<"&B4),5,COUNTIFS(ColD,A4,ColE,B4))