Re: Lookups across multiple sheets

[Andreas Guelzow <aguelzow pyrshep ca>]

On Thu, 2011-05-26 at 16:35 +0200, Mark Verboom wrote:
> Hi,
>
> I've recently been dyving a bit deeper into working with gnumeric and
> I've ran into a problem I can't seem to solve.
>
> I've got a spreadsheet with multiple pages. Each page contains two
> colums (A1:B100), where the first column is a unique number and the
> second is a date. On a seperate summery page I want to be able to type
> in one of the unique numbers and get the date that is next to it in one
> of the other sheets to appear in a cell.
>
> The only way I've found to do this is to make the very ugly construct
> with nested if's (where A6 is the cell I type in the unique number):
>
> =if(isna(vlookup(A6,sheet1!A11:B100,2,0)),
>   if(isna(vlookup(A6,sheet2!A11:B100,2,0)),
>   "Unknown",
>   vlookup(A6,sheet2!A11:B100,2,0)),
>  vlookup(A6,sheet1!A11:B100,2,0))
>
> This doesn't scale very well when using 7 or 8 sheets to lookup values
> and is pretty much hell to maintain :)
>
> Does anyone know a nice way to solve this?
>
> Thanks,
>
> Mark

I am not quite sure what your conditions are but something like this:

=index(array(Sheet1!B:B,Sheet2!B:B),1+vlookup(C1,array(Sheet1!
A:A,Sheet2!A:A),1,FALSE,TRUE))

In this case C1 holds what you are searching for

should be reasonably easy to maintain even for 7 or 8 sheets since
nothing needs to be changed if you add data rows but maybe I am missing
something (the above assumes that you are not searching for empty
values). 

Andreas

-- 
Andreas J. Guelzow, PhD, FTICA
Concordia University College of Alberta