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.

Country	City	Street
Austria	Wien	Bohemo
Austria	Wien	Thenok
Austria	Wien	Filator
Austria	Lienz	Abud
Austria	Lienz	Oksor

<tbody>
</tbody>

 

[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:

Country	Year	Code
Austria	1999	A1
Austria	2000	AB
Austria	2000	AG
Austria	2001	ZZ
Austria	2002	ZH
Poland	1998	A4
Poland	2000	AZ
Poland	2000	AF
Poland	2001	KL

<tbody>
</tbody>

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

Country	Year	Code		Austria
Austria	1999	A1		2000
Austria	2000	AB		CODES
Austria	2000	AG		AB
Austria	2001	ZZ		AG
Austria	2002	ZH
Poland	1998	A4
Poland	2000	AZ
Poland	2000	AF
Poland	2001	KL

<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>
</TBODY>

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))