Tricky - Add an additional column MATCH to INDEX formula

[thorpyuk]

Hiya,

I have a working index formula:

=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,1,0),0))

However, i current have the first 2 rows in my array as headers, and i want to match on first the top row, then the 2nd row. (currently matches top row only)#

I'm really scratching my head on this one, please can anyone help?

Thanks!

 

[T. Valko]

Hiya,

I have a working index formula:

=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,1,0),0))

However, i current have the first 2 rows in my array as headers, and i want to match on first the top row, then the 2nd row. (currently matches top row only)#

I'm really scratching my head on this one, please can anyone help?

Thanks!

If you want to match 2 header rows to define the column then what's the condition for the match of row 2?

You have for the first row:

MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,1,0)

You might want something like...

MATCH(1,IF(INDEX(Despatches_Weekly_Total_Splice,1,0)=$J$7,IF(Despatches_Weekly_Total_Splice,2,0)="this",1)),0),...

Sometimes named ranges make things more complicated!

 

[thorpyuk]

Hiya,

Thanks for the reply!

So...assuming i'm matching on the same criteria as row 1, do you mean this:

=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,MATCH(1,IF(INDEX(Despatches_Weekly_Total_Splice,1,0)=$J$7,IF(Despatches_Weekly_Total_Splice,2,0)=$J$7,1),0))


It gives me a #VALUE! error, so i assume it's not quite right :s

 

[T. Valko]

Hiya,

Thanks for the reply!

So...assuming i'm matching on the same criteria as row 1, do you mean this:

=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,MATCH(1,IF(INDEX(Despatches_Weekly_Total_Splice,1,0)=$J$7,IF(Despatches_Weekly_Total_Splice,2,0)=$J$7,1),0))


It gives me a #VALUE! error, so i assume it's not quite right :s

Argh!

My fault. I forgot to include the INDEX function for the inner IF call.

Array entered**:

=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,MATCH(1,IF(INDEX(Despatches_Weekly_Total_Splice,1,0)=$J$7,IF(INDEX(Despatches_Weekly_Total_Splice,2,0)=$J$7,1)),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[facethegod]

Hiya,

Thanks for the reply!

So...assuming i'm matching on the same criteria as row 1, do you mean this:

=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,LOOKUP(9.99999E+307,CHOOSE({1,2},MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,1,0),0),MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,2,0),0))))


It gives me a #VALUE! error, so i assume it's not quite right :s

I'm thinking you want to match on the first or second row if a match exists. If two instances exists the later would be picked:

Try..
=INDEX(Despatches_Weekly_Total_Splice,ROW()-6,LOOKUP(9.99999E+307,CHOOSE({1,2},MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,1,0),0),MATCH($J$7,INDEX(Despatches_Weekly_Total_Splice,2,0),0))))

Hope this helps

 

[thorpyuk]

Hi Guys,

Thank you so much

 

[T. Valko]

Hi Guys,

Thank you so much

You're welcome. Thanks for the feedback!