Multiple Criteria index, match formula

PGD15

Board Regular
Joined
Aug 5, 2016
Messages
137
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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))
 
Upvote 0
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 :(
 
Last edited:
Upvote 0
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))
 
Last edited:
Upvote 0
=IFERROR(INDEX(Monthly!D15,MATCH(K10,Monthly!A15,0)),INDEX(Monthly!D28,MATCH(K10,Monthly!A28,0)))
 
Upvote 0
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
 
Last edited:
Upvote 0
Dont worry I figured it out! :) I got it to work by adapting your formula. Thank you very much!
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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