Match/Index return #N/A

[DHS64]

I have used this formula
=INDEX($AF$7:$AN$14,MATCH(K22,$AE$7:$AE$13,0),MATCH(L22,$AF$6:$AN$6,0))

Works great as long yoou have responses in k22 and l22 but if those are not
yet filled in i get a #N/A is there any way to return a blank unless there
is something in those refrenced cells.

Thannks

[JE McGimpsey]

One way:

=IF(OR(ISNA(MATCH(K22,$AE$7:$AE$13,0)),ISNA(MATCH(L22,$AF$6:$AN$6,0))),
"", INDEX($AF$7:$AN$14,MATCH(K22,$AE$7:$AE$13,0),
MATCH(L22,$AF$6:$AN$6,0))

In article <0ED76E29-B3C7-420E-9F51-1140D4B8263A@microsoft.com>,
"DHS64" <DHS64@discussions.microsoft.com> wrote:

> I have used this formula
> =INDEX($AF$7:$AN$14,MATCH(K22,$AE$7:$AE$13,0),MATCH(L22,$AF$6:$AN$6,0))
>
> Works great as long yoou have responses in k22 and l22 but if those are not
> yet filled in i get a #N/A is there any way to return a blank unless there
> is something in those refrenced cells.
>
> Thannks

[Roger Govier]

Hi

One way
=IF(OR(K22="",L22=""),"",INDEX($AF$7:$AN$14,MATCH(K22,$AE$7:$AE$13,0),MATCH(L22,$AF$6:$AN$6,0)))

Regards

Roger Govier


DHS64 wrote:
> I have used this formula
> =INDEX($AF$7:$AN$14,MATCH(K22,$AE$7:$AE$13,0),MATCH(L22,$AF$6:$AN$6,0))
>
> Works great as long yoou have responses in k22 and l22 but if those are not
> yet filled in i get a #N/A is there any way to return a blank unless there
> is something in those refrenced cells.
>
> Thannks