Using variables in index/match formulas

**kagerber** · 06-14-2012, 12:51 PM

3 questions...

1. I have a very long index/match formula matching 3 columns in two different worksheets. One of my worksheets names will keep changing, and I need a variable name instead of the worksheet name. How do I do that? In the example below, I'd like to replace [longsheetname.xlsx]longtabname with a variable I define, like ShortNm

=INDEX([longsheetname.xlsx]longtabname!$AF$1:$AF$999,(MATCH($A2&$B2&$C2,([longsheetname.xlsx]longtabname!$E$1:$E$999)&([longsheetname.xlsx]longtabname!$F$1:$F$999)&([longsheetname.xlsx]longtabname!$L$1:$L$999),0)),1)"

2. I'd like to have a variable name instead of the range, since I'd like to have the users set the range for different lookups.
So above, instead of writing $AF$1:$AF$999, I'd like to write something like Design_1

3. Is there a better way to do the other Match ranges instead of $E$1:$E$999 since the length of the spreadsheet may be variable? It is around 150 rows long, so I put 999 to make sure it was long enough, but it isn't very elegant.

In addition, I keep running up against the 255 char max in my VBA code formula. So I can't make the formula much longer.

**dilipandey** · 06-14-2012, 01:00 PM

Hi Kagerber,

1) You can use Indirect formula to pick up [longsheetname.xlsx]longtabname in formula from a cell. After this all workbooks need to be opened for the formula to calculate and work.
2) Instead of writing $AF$1:$AF$999, you can use a name, select the range and enter a name on a filed which is on left side of formula bar
3) Use a dynamic named range to accommodate data range changes.

Regards,
DILIPandey

<click on below 'star' if this helps>

**kagerber** · 06-14-2012, 02:57 PM

Indirect function is not working for me. What am I doing wrong?

file1 = "longfilename.xlsx"
tab1 = "longtabname"
range1 = "$AF$1:$AF$999"

Selection.FormulaArray = _
"=INDEX(indirect("[" & file1 & "]" & tab1 & "!" & range1),(MATCH(...))