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.
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.