# Thread: INDEX/MATCH with Dynamic Ranges Thanks: 0 Likes: 0

1. ## INDEX/MATCH with Dynamic Ranges

Using Excel2007
Workbook contains 2 sheets,Combined and Reports
On Combined, columnB=Invoice No, C=Account No, E=Invoice Date
On Reports, columnAS=Invoice No, AT=Account No, AU=Invoice Date, AV=Currency
(the other columns on each sheet are not relevant at the moment)
Created Named Ranges as follows:
SAcc= =OFFSET(Reports!\$AT\$5,0,0,COUNTA(Reports!\$AT:\$AT),1)
SDte= =OFFSET(Reports!\$AU\$5,0,0,COUNT(Reports!\$AU:\$AU),1)
SRef= =OFFSET(Reprts!\$AS\$5,0,0,COUNTA(Reports!\$AS:\$AS),1)
SFnd= =OFFSET(Reports!\$AS\$5,0,0,COUNTA(Reports!\$AS:\$AS),3)
If I use Range("D5").FormulaArray="=INDEX(Reports!AV\$5:AV\$1932,MATCH(B5&C5&E5,SRef&SAcc&SDte,0))" I get the result I want, but if I change the formula to "=INDEX(Reports!AV\$5:AV\$1932,MATCH(B5&C5&E5,SFnd,0))" I get the A Value is no available to the formula or function error.
Have tried changing SFnd to only include 2 columns and using SFnd&SDte in the formula but I still can't get the right result.
This becomes even more important in a formula I need later where I am coming up against the 255 character limit.

2. ## Re: INDEX/MATCH with Dynamic Ranges

Your first formula matches with a concatenated array. Your second formula is trying to match with a range of 3 columns, which is not the same thing.

3. ## Re: INDEX/MATCH with Dynamic Ranges

Sorry for the delay in replying. I see what you mean, it's nice that when I'm having a "dumb" day you are there to point me in the right direction
Thanks again

## User Tag List

#### Posting Permissions

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