Find & 2-way-offset

  • Thread starter Thread starter Jouni
  • Start date Start date
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
 
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)
 
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
 
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.
 
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.
Click to expand...
 
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.
 
OOPS. Sorry Leo. I hadn't scrolled down enough to see you'd solved it
already. Worked on it for nothing. <g>
 
Back
Top