How to use LOOKUP from two different sheets?

Question

I have a Calc document that has N sheets. First sheets are equal and contain some data. Last sheet is a summary.

My problem is that I need to use the LOOKUP function in the last sheet to look up for data in the N-1 previous sheets. This doesn't work:

=LOOKUP(X, Sheet1.A1:Sheet9.A100, Sheet1.B1:Sheet9.B100)

In this case it's valid a solution that requires to hardcode the sheets, i.e.:

=LOOKUP(X, FANTASTIC_JOIN_FUNCTION(Sheet1.A1:A100;Sheet2.A1:A100;Sheet3.A1:A100; ... ), FANTASTIC_JOIN_FUNCTION(Sheet1.B1:B100;Sheet2.B1:B100;Sheet3.B1:B100; ...))

but I don't know which is this FANTASTIC_JOIN_FUNCTION...

Any idea?

Does the data contain numbers or strings? – Jim K Nov 11 '16 at 06:13 — Jim K, Nov 11 '16 at 06:13
@JimK Data. A1:A100 are dates and B1:B100 numbers. – Ivan Nov 11 '16 at 10:57 — Ivan, Nov 11 '16 at 10:57

score 1 · Answer 1 · answered Nov 11 '16 at 06:12

One way is to put this in cell A1 (or somewhere else that is convenient) on the summary sheet:

=LOOKUP(X, Sheet1.$A$1:$A$100, Sheet1.$B$1:$B$100)

In cell A2 put this:

=LOOKUP(X, Sheet2.$A$1:$A$100, Sheet2.$B$1:$B$100)

Continue this way for N-1 rows.

Now we need to get rid of #N/A errors. To do this, put this in cell B1:

=IF(ISNA(A1),"",A1)

Then fill this formula down N-1 rows.

Finally, merge the values into a single cell. For numerical data. this works:

=SUM(B1:B100)

For text data, concatenate all N-1 cells in the B column, for example:

=B1&B2&B3&B4&B5

This is not as clean as I'd like, but I'll work :) Thanks – Ivan Nov 11 '16 at 11:00 — Ivan, Nov 11 '16 at 11:00

How to use LOOKUP from two different sheets?

1 Answers1