Index and match help

[Sandy Carruthers]

Hi,

I'm having problems with an Index and Match forumla (see excel help attached).

You'll see that the formula is only returning the minimum range avialble rather than the full range between the start date and end date.

I've been looking at this for days now and it's driving me round the bend. I assume it's just a tweek here and there.

Any help please.

 

[Somendra Misra]

Hi Sandy,

Welcome to chandoo.org forum.

Try below array formula in C12 and copy across and down.

=INDEX($H$2:$H$3,MATCH(1,(C$11>=$C$2:$C$3)*(C$11<=$D$2:$D$3)*($A12=$A$2:$A$3),0))

Regards,

 

[Sandy Carruthers]

Thanks for the quick reply and also for a working solution.

What does the "1" represent.......MATCH(1, ........ Just for future reference.

 

[Somendra Misra]

@Sandy Carruthers

1 is equivalent to TRUE, since in lookup array argument of MATCH function we are multiplying comparison operation which will convert the TRUE & FALSES's to 1 & 0. So MATCH will match 1 in this array.

Regards,