help with a INDEX and MATCH formula

[tlacloche]

i have an empty column which i want to be filled in based on the header and sub header of another column.
I want cell R18 to be matched with row 1 AND cell R22 to be matched with row 2.
so the formula in column N can pinpoint exactly which column i want copied over.

this is the formula i currently have but im getting the VALUE! error in the cell

Please Login or Register  to view this content.

please help.
thanks

[NBVC]

Try:

=INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)+MATCH($R$22,$B$2:$M$2,0)-1)

Note: Fix the formula in R22 so that result matches the headers in B2:M2..... i.e. change to: =CHOOSE(R21,"Total","Jan-June","Jul-Dec")

[tlacloche]

great. thanks mate.
what would the formula be in column O. i want it to have the "Last Years Avg" column from the respective section based on cell R18.
Please bare in mind that column orders may change in the future so preferably the formula should be matching words like in your previous post

[NBVC]

Try:

=INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)+MATCH(MID(O$2,4,20),$B$2:$M$2,0)-1)

or

=INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)+MATCH(SUBSTITUTE(O$2,"FT ",""),$B$2:$M$2,0)-1)

[tlacloche]

silly me. i forgot to mention that if the "Last Years Avg." checkbox is unchecked, then column O doesnt have values thus the chart does not show that series.
But again, Please bare in mind that column orders may change in the future so preferably the formula should be matching words

[NBVC]

Try:

=IF($S$32=TRUE,INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)+MATCH(SUBSTITUTE(O$2,"FT ",""),$B$2:$M$2,0)-1),0)

[tlacloche]

we're getting there...
can you make the column become an error when the check box is unticked? because as it is, the 0 value is displayed on the chart as 0 whereas i dont want it o be there at all.
btw, its R32 not S32.

appreciate all this help buddy

[NBVC]

Ok

=IF($S$32=TRUE,INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)+MATCH(SUBSTITUTE(O$2,"FT ",""),$B$2:$M$2,0)-1),NA())

[tlacloche]

that works perfectly thanks.
however, ive just inserted a few columns in random places and it messes up the results.
as i mentioned earlier, i was hoping the formula can work even if columns are moved around. doesn't the formula work by matching text so theres no reason it shouldn't work, unless im missing something.

[NBVC]

It kind of depends.

I assumed that each group (i.e. Profits, Sales, Costs) would be consist in there subcategories (i.e. Jan-June,Jul-Dec,Total,Last Year Avg) so that if you add a column in one of those, then the same would be happening in the others. Is that not the case?

[tlacloche]

not necessarily.
the idea is that anything can happen in the future. more and more data is being provided so more and more colunms will be added. and i dont know where they'll be placed.
for this reason, i was hoping the formula in column N can simply match cell R18 across the whole of row 1 whilst R22 is matched across row 2.
and the column O formula can match cell R18 across the whole of row 1 whilst R32 (or a helper cell if needed) is used for across row 2.
so regardless of whether columns are added in random locations, the matches should still occur.

in theory it sounds simple and feasible.
is it different in reality?

[NBVC]

Ok, try:

=INDEX(INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)):$M3,MATCH($R$22,INDEX($B$2:$M$2,1,MATCH($R$18,$B$1:$M$1,0)):$M$2,0))

[tlacloche]

works perfectly.
could you help me with the checkbox formula please

[NBVC]

Sorry, what was the question about the checkbox?

[tlacloche]

what would the formula be in column O. i want it to have the "Last Years Avg" column from the respective section based on cell R18 when the checkbox is ticked.
however, when it is not ticked, i would like an error in column O so it is not present in the chart.
again, if you could please do it so if columns are added/moved around it will still work (just like you did with post #12.

thanks

[NBVC]

That should be:

=IF($R$32=TRUE,INDEX(INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)):$M3,MATCH(MID(O$2,4,20),INDEX($B$2:$M$2,1,MATCH($R$18,$B$1:$M$1,0)):$M$2,0)),0)

[tlacloche]

its not working.
when i tick the box, i get #N/A error. when the box is not ticked, i get a 0 for all rows.

[NBVC]

In your provided sample the formula I provided I get non-zero results.

The only problem I got was that your header in I2 was inconsistent and had a trailing period that you need to remove.

[tlacloche]

here's my workbook

[NBVC]

You changed the O2 header!

Try:

=IF($R$32=TRUE,INDEX(INDEX($B3:$M3,1,MATCH($R$18,$B$1:$M$1,0)):$M3,MATCH($O$2,INDEX($B$2:$M$2,1,MATCH($R$18,$B$1:$M$1,0)):$M$2,0)),0)

[tlacloche]

oh my. apologies.
the formula above gives "0" when the checkbox is unticked. i want an error remember, so its not visible on the chart

[NBVC]

so just replace the 0 with NA() as before....

[tlacloche]

we got there eventually (i mean you got there eventually).
cant thank you enough.
legend

[tlacloche]

i was playing around with macros to make the checkbox grey out when the 2nd and 3rd radio buttons are selected. but this was proving way to complicated, especially as excel on mac doesnt take activex.

so i was thinking, surely there's a way to add to the formula you gave:

Please Login or Register  to view this content.

...which can also make column O become #N/A errors if the 2nd and 3rd button is selected by referring to the cell R22.
so if R22 is either Jan-Jul or July-Dec, column O is #N/A.

thanks

[tlacloche]

i was playing around with macros to make the checkbox grey out when the 2nd and 3rd radio buttons are selected. but this was proving way to complicated, especially as excel on mac doesnt take activex.

so i was thinking, surely there's a way to add to the formula you gave:

Please Login or Register  to view this content.

...which can also make column O become #N/A errors if the 2nd and 3rd button is selected by referring to the cell R22.
so if R22 is either Jan-Jul or July-Dec, column O is #N/A.

thanks