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

A4 and B4 are cells in which you have chosen country and year.

J.Ty.

 

[Forestq]

thanks J.Ty.

I have already implemented into my file and works great!

DZIKUJE ZA POMOC!
POZDRAWIAM

 

[Forestq]

hi,

I want to add unfortunately one more lvl, so I did:

for 3 lvl

OFFSET(Start,COUNTIF(colCA,"<"&'Summary'!$A$9)+COUNTIFS(colCA,'Summary'!$A$9,colCB,"<"&'Summary'!$F$9),18,COUNTIFS(colCA,'Summary'!$A$9,colCB,'Summary'!$F$9))

is ok, start = ='Summary'!$BK$2

now I add one more lvl but something is wrong:

=OFFSET(Start,COUNTIF(colCF,"<"&'Summary'!$A$9)+COUNTIFS(colCF,'Summary'!$A$9,colCG,"<"&'Summary'!$F$9)+COUNTIFS(colCF,'SNTC Opportunity Summary'!$A$9,colCG,"<"&'Summary'!$F$9,colCH,"<"&'Summary'!$H$9),24,COUNTIFS(colCF,'Summary'!$A$9,colCG,'Summary'!$F$9,colCH,"<"&'Summary'!$H$9))

Are you able to help me?

 

[Forestq]

I think I found mistake:

start ='Summary'!$BK$1

=OFFSET(Start,COUNTIF(colCF,"<"&'Summary'!$A$9)+COUNTIFS(colCF,'Summary'!$A$9,colCG,"<"&'Summary'!$F$9)+COUNTIFS(colCF,'SNTC Opportunity Summary'!$A$9,colCG,'Summary'!$F$9,colCH,"<"&'Summary'!$H$9),24,COUNTIFS(colCF,'Summary'!$A$9,colCG,'Summary'!$F$9,colCH,'Summary'!$H$9))

 

[J.Ty.]

Dos it finally work properly?


J.Ty.

 

[Forestq]

yes, works!

Do you maybe know how long function I can insert into excel field?

Becuase I need to do a couple of "if" in one function to take all cases when I will be selecting last 4th level.

I wrote it, but I can`t put into excel field. When I removed some "if" it`s ok. I`m thinking that function can be too long.

 

[J.Ty.]

For Excel 2010, from http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx:

• Length of formula contents 8,192 characters
• Internal length of formula 16,384 bytes
• Arguments in a function 255
• Nested levels of functions 64

J.Ty.

 

[Forestq]

Hi J.Ty,

something is still wrong with my formula:

=OFFSET(Start,COUNTIF(colCF,"<"&'Opportunity Summary'!$A$9)+COUNTIFS(colCF,'Opportunity Summary'!$A$9,colCG,"<"&'Opportunity Summary'!$F$9)+COUNTIFS(colCF,'SNTC Opportunity Summary'!$A$9,colCG,'Opportunity Summary'!$F$9,colCH,"<"&'Opportunity Summary'!$H$9),24,COUNTIFS(colCF,'Opportunity Summary'!$A$9,colCG,'Opportunity Summary'!$F$9,colCH,'Opportunity Summary'!$H$9))

start='sheet1'!$BK$1

for the first values is ok, but on the last values is wrong and I don't know why or what is wrong?

 

[J.Ty.]

Hi J.Ty,

something is still wrong with my formula:

=OFFSET(Start,COUNTIF(colCF,"<"&'Opportunity Summary'!$A$9)+COUNTIFS(colCF,'Opportunity Summary'!$A$9,colCG,"<"&'Opportunity Summary'!$F$9)+COUNTIFS(colCF,'SNTC Opportunity Summary'!$A$9,colCG,'Opportunity Summary'!$F$9,colCH,"<"&'Opportunity Summary'!$H$9),24,COUNTIFS(colCF,'Opportunity Summary'!$A$9,colCG,'Opportunity Summary'!$F$9,colCH,'Opportunity Summary'!$H$9))

start='sheet1'!$BK$1

for the first values is ok, but on the last values is wrong and I don't know why or what is wrong?

What does it mean, that this formula is ok for the first values, and is wrong for the last ones?
I guess, that Excel accepts it, but it gives wrong list of choices for the elements at the end -am I right?

J.Ty.

 

[Forestq]

yes, you are right,

I did exeption, that for the last values, excel is taking value from other list, but I`m still wondering why for the first couple of items is ok, and for the rest is wrong