# Index, Match and Indirect giving REF error

1. ## Index, Match and Indirect giving REF error

Hi everyone,

I have seen many posts on a similar subject and I have tried to adapt posted solutions to my formulae to suit without any success. Basically, i have a cell L3 on a worksheet which contains a worksheet name from within a different file called Bristol & Taunton Book List All Freqs.xlsx. When I use this formulae below, it returns the data as expected without a problem. the worksheet name in this instance is 16 and both files are open.

=INDEX('[Bristol & Taunton Book List All Freqs.xlsx]16 '!\$B:\$B,MATCH(C8,'[Bristol & Taunton Book List All Freqs.xlsx]16 '!\$D:\$D,0))

When I try to incorporate INDIRECT in to the formulae as shown below, the REF error is displayed

=INDEX(INDIRECT("'[Bristol & Taunton Book List All Freqs.xlsx]"&\$L\$3&" '!\$B:\$B"),MATCH(C8,INDIRECT("'[Bristol & Taunton Book List All Freqs.xlsx]"&\$L\$3&" '!\$D:\$D,0")))

Am I trying to do something that is not possible?

Any help that you can give would be greatly appreciated.

many thanks,

Stan

2. ## Re: Index, Match and Indirect giving REF error

You have included the zero from the MATCH function inside the 2nd INDIRECT - that should be outside - try this version

=INDEX(INDIRECT("'[Bristol & Taunton Book List All Freqs.xlsx]"&\$L\$3&" '!\$B:\$B"),MATCH(C8,INDIRECT("'[Bristol & Taunton Book List All Freqs.xlsx]"&\$L\$3&" '!\$D:\$D"),0))

3. ## Re: Index, Match and Indirect giving REF error

What can I say? You are a legend. That is so frustrating!!! I looked at this for what seemed like hours, Changing this, changing that. You have saved me a lot of hassle. Thanks again for your help.

Stan

4. ## Re: Index, Match and Indirect giving REF error

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -

Incase your issue is not solved, you can undo it as follows -

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

There are currently 1 users browsing this thread. (0 members and 1 guests)