# Can an array input be used in the sheet name of vlookup's range?

I have a formula which works, but is absurdly long, but repeats the core data over and over.

My spreadsheet contains 70 pages describing sets of cards. The page I'm working on gives me a particular summary by using the card identifier (in A3) as the index. More specifically, I want the minimum price for a card by that identifier which may appear in any of the sets. All of the sets have less than 500 cards (so the search range is B3:I500)

The names do not follow a pattern, despite the simplification I made here, but are listed in a row G2:Z2 (obviously incomplete at the moment)

``````=MIN(
IFERROR(VLOOKUP(\$A3,Set1!\$B\$3:\$I\$500,6,FALSE),99999),
IFERROR(VLOOKUP(\$A3,Set2!\$B\$3:\$I\$500,6,FALSE),99999),
IFERROR(VLOOKUP(\$A3,Set3!\$B\$3:\$I\$500,6,FALSE),99999), ...
``````

[ pattern repeats for the other sets...]

I use INDIRECT in another part of the spreadsheet and array notation yet again elsewhere.

``````VLOOKUP(\$A3,INDIRECT("'"&M\$2&"'!\$B\$3:\$I\$516"),6,FALSE)
``````

I do not wish to merge the pages (which I admit would simplify things tremendously in some regards).

Is there a way to simplify this large expression into a shorter one that achieves the same goal (and hopefully will avoid further editing as I add the rest of the data)?

I was trying to get something working with indirect, vlookup and array but I always get #VALUE errors, probably due to mangling the syntax.

I am not looking for a VBA solution, nor the straight forward "convert it to a database" answer, but any help is appreciated.

-
just to be clear, if there were 10 matches on the card identifier, I would like all 10 of the results from column 6 returned so that min can choose the least one. –  Stephen Sep 23 '11 at 15:29
One quick thing: if you're using Excel 2007 or newer, you can use IFERROR to avoid repetition. For example, `=IFERROR(VLOOKUP(\$A3,Set1!\$B\$3:\$I\$500,6,FALSE),99999)` –  Excellll Sep 23 '11 at 16:48
That tip helps simplify a lot of isna abuse :) –  Stephen Sep 23 '11 at 20:31
To answer the question in the title: Unfortunately, you can't use 3D references with VLOOKUP, either as a source reference or as the lookup value. More info here: office.microsoft.com/en-us/excel-help/… –  Ellesa Sep 27 '11 at 4:16
@Kaze If you put it in an answer, I'll accept that. –  Stephen Sep 27 '11 at 10:46

To answer the question in the title: Unfortunately, you can't use 3D references with VLOOKUP, either as a source reference or as the lookup value. More info here: http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx

Your formula seems simplified enough, though it is quite long. Though if your workbook gets larger, I'd suggest using `INDEX/MATCH` instead of `VLOOKUP` for faster calculations.

-

You could put the lookups in the `Set#` sheets.
If your formula is on sheet called `Summary`:
Put a formula in say cell `I1` on each `Set` sheet

``````=IFERROR(VLOOKUP(Summary!A3,\$B\$3:\$I\$500,6,FALSE),99999)
``````

and then in `Summary` sheet put

``````=MIN('Set1:Set70'!I1:I1)
``````

Assumes the `Set` sheets are grouped together (ie no other sheets betwenn them)

-
Unfortunately the summary sheet has 100 entries of interest, that change somewhat frequently, so I would forced to put 100 of such entries in (say) column I, which seems like more work than expanding the existing formula. Thanks for the idea of turning the lookup around however. –  Stephen Sep 26 '11 at 12:30