Formula with option if not found then

[dutchmaste]

I have a formula:

=INDEX(INDIRECT("'"&C2&"'!C2:J69"),MATCH(C8,INDIRECT("'"&C2&"'!'Title 1.1'!B2:B69"),INDIRECT("'"&C4&"'!'Title 1.1'!A2:A69")),MATCH(C6,INDIRECT("'"&C2&"'!'Title 1.1'!C1:J1"),0))

I was hoping for the C8 that if not found it would search for the C4 in same range. But this formula is not working and I can't figure it out.

I attached this file with two samples with answers next to it how it should turn out.
testnvwa.xlsx

[CRIMEDOG]

MATCH(C8,INDIRECT("'"&C2&"'!'Title 1.1'!B2:B69"),INDIRECT("'"&C4&"'!'Title 1.1'!A2:A69")),

is wrong - the second indirect should not be there needs to be MATCH(C8,INDIRECT("'"&C2&"'!'Title 1.1'!B2:B69"),0), that will be the row

[CRIMEDOG]

What you might do is start with
=if(Countif(INDIRECT("'"&C2&"'!'Title 1.1'!B2:B69"))>0,INDEX(INDIRECT("'"&C2&"'!C2:J69"),MATCH(C8,INDIRECT("'"&C2&"'!'Title 1.1'!B2:B69"),0),MATCH(C6,INDIRECT("'"&C2&"'!'Title 1.1'!C1:J1"),0)),INDEX(INDIRECT("'"&C2&"'!C2:J69"),MATCH(C4,INDIRECT("'"&C2&"'!'Title 1.1'!B2:B69"),0),MATCH(C6,INDIRECT("'"&C2&"'!'Title 1.1'!C1:J1"),0))

[kvsrinivasamurthy]

May be this

Please Login or Register  to view this content.

[CRIMEDOG]

nice kvsrin...

[dutchmaste]

hmm I keep getting a ref# as result on your formula kvsrin

I also tried altering it a little bit as the Title 1.1 is only applicable if selected in C2 of input. But still got REF# attached the updated file.

testnvwa.xlsx

[kvsrinivasamurthy]

G2 had extra space. Now removed.

[dutchmaste]

Works like a charm. Thank you.