Multiple Criteria index, match formula

[PGD15]

Hi there,

got an issue with an index match formula im trying to do.

I want to say if the value in cell K10 (report tab) is equal to either a15:b15 or a28:b28 (merged cells in monthly tab).

then i want to return the value between d15:au15 or d28:au28 (both monthly tab) respectively IF the value in cells D2:au2 (monthly tab) is equal to that in d11 (report tab)

I hope that makes sense.

 

[Oscar Cronquist]

Can you explain

the value between d15:au15

in greater detail?

 

[Special-K99]

It's not worded very well.

Looking at this again I think OP is after retrieving a value from D15:AU28, where there is a matching column between Report!D11 and Monthly!D2:AU2
and the row to retrieve is either row 15 or row 28 dependent on Report!K10 matching A15:B15 or A28:B28

Try (this assumes Report!K10 MUST match either A15:B15 or A28:B28)

=INDEX(Monthly!D15:AU28,IF(COUNTIF(A15:B15,Report!K10)<>0,1,14),MATCH(Report!D11,Monthly!D2:AU2))

 

[PGD15]

It's not worded very well.

Looking at this again I think OP is after retrieving a value from D15:AU28, where there is a matching column between Report!D11 and Monthly!D2:AU2
and the row to retrieve is either row 15 or row 28 dependent on Report!K10 matching A15:B15 or A28:B28

Try (this assumes Report!K10 MUST match either A15:B15 or A28:B28)

=INDEX(Monthly!D15:AU28,IF(COUNTIF(A15:B15,Report!K10)<>0,1,14),MATCH(Report!D11,Monthly!D2:AU2))

I get the error #ref ... I shall try to make this clear.

Based on the following criteria I want to return a value from either of these two ranges (this is my issue)
d15:au15 or d28:au28 (monthly tab)

the value of which range to use to will be located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example)

to select which column from d to au needs to be selected within the two ranges I need to match the value in C11 (report tab) to the range d3:au3 (monthly tab)

Hope this clears it up I dont know if this can be done with multiple ranges that is my issue.

- this might work using d15:au28 as you suggested but i tend to get error messages or maybe my previously poor wording messed the formula up

 

[Special-K99]

I guess this is a typo

Based on the following criteria I want to return a value from either of these two ranges (this is my issue)
d15:au15 or d28:au28 (monthly tab)

the value of which range to use to will be located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example)


With the existence of a Report sheet and a Monthly sheet this works (syntactically, I havent checked with data)

=INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))

 

[bob_obo]

=IFERROR(INDEX(Monthly!D15,MATCH(K10,Monthly!A15,0)),INDEX(Monthly!D28,MATCH(K10,Monthly!A28,0)))

 

[PGD15]

I guess this is a typo

Based on the following criteria I want to return a value from either of these two ranges (this is my issue)
d15:au15 or d28:au28 (monthly tab)

the value of which range to use to will be located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example)


With the existence of a Report sheet and a Monthly sheet this works (syntactically, I havent checked with data)

=INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))

Thanks, almost works just 1 slight issue. what is the l10 cell pointing too? as l10 is going to equal a year such as 2017 then the text YTD Total

so this cell will either say 2017 ytd total or 2018 ytd total and this will be rolling. so if i change my target year to 2017 the formula doesn't pick up the 2017 data (like it should from the cell ranges i gave you)

I believe thw 1,1,14 my be directing/matching it slightly wrong. as if it 2017 this should return the values in the row 15 if 2018 values in row 28 (which it currently does)


ignoore the above this bit below is the edit of my problem

wait i f'ed up i forgot to say the cell range L10 needs to be matched to either a15 or or a28 in addition to everything else

 

[PGD15]

Dont worry I figured it out! I got it to work by adapting your formula. Thank you very much!

 

[Special-K99]

You said this

"located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example) "

So thats what I put in the formula.

=INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))

Now youre saying its neither of them.
You need to make your mind up.

 

[PGD15]

You said this

"located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example) "

So thats what I put in the formula.

=INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))

Now youre saying its neither of them.
You need to make your mind up.

I know, but i said (example) as these werent the actual values, then i realised you used them and i noticed so i changed these to the correct values and it works.

Many thanks for the help