Problem with IF(INDEX,(Match))

I have a dropdown list that references seperate worksheets. I am trying to pull data (text) but only from tables on each sheet dependent on the name in the list. The following formula works only for the first IF statement, if I select a different name I get a FALSE statement. I will have a total of 5 sheets and 5 names on the list but only am showing 2 names for clarity.

Please Login or Register  to view this content.

At present the sheets as shown in the formula have the data in the same columns on each sheet. Not all 5 sheets will have the data in the same column but if necessary I can shift the data.

Thanks

Jim O

A missing parenthesis...

Try:

Please Login or Register  to view this content.

Could possibly be shortened to?:

=IF($F$2="","",INDEX(INDIRECT("'"&$F$2&"!$D$2:$D$78"),MATCH($F$4,INDIRECT("'"&$F$2&"!$C$2:$C$78"),0)))

this is a volatile formula though.... because of INDIRECT

I missed a couple of apostrophes.. .my apologies...

=IF($F$2="","",INDEX(INDIRECT("'"&$F$2&"'!$D$2:$D$78"),MATCH($F$4,INDIRECT("'"&$F$2&"'!$C$2:$C$78"),0)))