up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
100% accept rate
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
1  
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/… – Kaze 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
feedback

2 Answers

up vote 0 down vote accepted

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.

link|improve this answer
feedback
up vote 1 down vote

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)

link|improve this answer
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
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.