Find & 2-way-offset

J

Jouni

Hi all,

I'm searching a cell and a value in it. I know that in column A there's a
text string that I'm interested in, but I don't know the row. I also know
that the value I'm interersted in is in a cell 3 columns right and 24 rows
down from the column A cell. With VLOOKUP I'm able to catch the "3 cols
right" part, but how can I refer to the cell 24 rows down from that? I know
there's OFFSET worksheet function, but I'm not able to use it with VLOOKUP.
Neither I can come up with a solution with HLOOKUP. Any help?

Thanks in advance,
Jouni
 
B

Bob Phillips

Jouni,

Try this

=INDIRECT(CHAR(COLUMNS(A:A)+64+3)&MATCH("abc",A:A,0)+24)


It's looking for a value of "abc", so change that MATCH("abc", to
MATCH(text_value

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

Jouni

Hi Bob,

it's close. It works when source data and the formula are on the same sheet,
but when the formula is on another sheet than the source data, then it
returns something odd. I have:
=INDIRECT(CHAR(DataDetailed!A:A+64+3)&MATCH("> Osakkeet >
"&$B11,DataDetailed!A:A,0)+24)
where DataDetailed is the name of the data source sheet and "> Osakkeet >
"&$B11 is what I'm looking for.

It still seems to return to return a value from the sheet where the formula
is and not from "DataDetailed". However, I don't understand the logic
because the value doesn't come from a cell 3 steps right and 24 down...
There's actually a lot in your formula that I don't really understand.

Thanks again,
Jouni
 
L

Leo Heuser

Hi Jouni

One way:

In a cell in Sheet1:

=OFFSET(Sheet2!A1,MATCH("Text",Sheet2!A:A,0)+23,3)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
J

Jouni

Thanks Leo, works perfectly!

Jouni

Leo Heuser said:
Hi Jouni

One way:

In a cell in Sheet1:

=OFFSET(Sheet2!A1,MATCH("Text",Sheet2!A:A,0)+23,3)

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
E

Earl Kiosterud

Jouni,

How about this?:

=OFFSET(A1,MATCH(D1,A2:A1000,0)+24,3,1,1)

D1 contains the text string, and it's presumed that it lives in a cell in
column A in by itself (not part of a larger text string -- I wasn't sure
about that part of your problem). The first entry in the column is A2.
 
E

Earl Kiosterud

OOPS. Sorry Leo. I hadn't scrolled down enough to see you'd solved it
already. Worked on it for nothing. <g>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top