Referencing relative to a formula reference

[Csaba Gabor]

I have an Excel workbook with two worksheets. In one
sheet (Books) I have defined a series of items with
column 1 being my book Id and column 4 being the book
title. In the second sheet (ISBN) I may have multiple
entries per line of the first sheet, since a given book may
have multiple editions, each with its own ISBN number.

In column 2 of ISBN I have the BookId from the first
sheet, which I have as an absolute reference (eg. the
formula would be something like =Books!R4C1). Now
here's my problem. I'd like to have a column in ISBN
which reflects the book's title that is indicated by the
reference in column 2 (BookId). I thought I could do
something along the lines of
=OFFSET(INDIRECT(RC2,FALSE),0,3) but
INDIRECT is quite unhappy, and all the functions
I remember for dealing with formulas are for macro
sheets (eg. GET.CELL(6,R2C)).

Now I can get the desired effect by doing
=OFFSET(Books!R1C4,MATCH(RC2,Books!C1,0)-1,0)
but this seems computationally burdensome on
Excel, and I have to wonder if there isn't a more
direct way I'm overlooking.


There is a second issue. Using the method in the
above paragraph works, except that when the
returned string has a hard newline (that I previously
entered via Alt+Enter), this is shown as a box
character and no longer shows as a newline.

Any tips?
Thanks,
Csaba Gabor from Vienna

 

[Bob Phillips]

Just use

=VLOOKUP(RC2,Books!C1:C4,4,FALSE)

 

[Csaba Gabor]

VLOOKUP requires sorted order of the lookup column, which
was implied to be not the case by my having a third argument
of 0 for MATCH. But even if that were not the case, VLOOKUP
is essentially the same technology as MATCH, HLOOKUP,
and LOOKUP and does not address either of my questions.

Q1: In a spreadsheet, is there any formula that can deal
with the formula in another cell or the reference returned
by such formula (eg. =Books!R4C1)?

Q2: When MATCH returns a cell value that has a hard newline
(entered via Alt+Enter), how does one get that to display as
a newline instead of showing a box symbol?

Just in case, this is on Excel 2003 under Win XP Pro

Csaba

 

[Bob Phillips]

VLOOKUP requires sorted order of the lookup column, which
was implied to be not the case by my having a third argument
of 0 for MATCH. But even if that were not the case, VLOOKUP
is essentially the same technology as MATCH, HLOOKUP,
and LOOKUP and does not address either of my questions.

It doesn't if you use FALSE as the last argument as I did.

It addresses it as I read it, not directly because I believe there is a
better way than your approach, but the objective.