# Problem with INDEX, INDIRECT, MATCH, MATCH

1. ## Problem with INDEX, INDIRECT, MATCH, MATCH

I have been unable to get the Indirect portion of a formula to work.

The following formula works but when I tried to include an Indirect Index all I get is #REF!

``Please Login or Register  to view this content.``

This is how I tried to add the Indirect portion.

``Please Login or Register  to view this content.``
Thanks

Jim O

2. ## Re: Problem with INDEX, INDIRECT, MATCH, MATCH

Can you attach a file? Hard to debug a formula with no context.

3. ## Re: Problem with INDEX, INDIRECT, MATCH, MATCH

I will send a file as soon as I can put together a sample. The actual file is a little involved so I will strip out the unnecessary data.

As an aside the first formula works but I want to use Indirect inplace of the '2011'! value so I can easily refference different years (sheet tabs) from a dropdown list. The \$L\$3 cell will contain the dropdown list, all other refferences would remain the same.

Jim O

4. ## Re: Problem with INDEX, INDIRECT, MATCH, MATCH

Hello,

the Indirect() statement is not formed properly.

INDIRECT(\$L\$3&"!"&DJ\$9:DJ\$24)

This will take the content of cells DJ9 to DJ24 and will try to interpret that as a range. I think you really want

INDIRECT(\$L\$3&"!DJ\$9:DJ\$24")

Try

=INDEX(INDIRECT(\$L\$3&"!DJ\$9:DJ\$24"), MATCH(\$O\$3,INDIRECT(\$L\$3&"!\$A\$9:\$A\$24"),0), MATCH(\$M18,INDIRECT(\$L\$3&"!DJ\$3"),0))

5. ## Re: Problem with INDEX, INDIRECT, MATCH, MATCH

teylyn,

Here is a sample file that I hope will make things clearer.

Jim O

6. ## Re: Problem with INDEX, INDIRECT, MATCH, MATCH

teylyn,

Once I got my refferences in order your formula works perfectly.

Thank you and bentleybob very much for your time and input.

Jim O

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1