Offset & Indirect Function

[Guest]

does anyone know how to use the indirect and offset worksheet function to
return the value of a cell base based on a where the vlookup value is found
on a work sheet

 

[Bob Phillips]

Give an example of your data. VLOOKUP normally offsets by a column, which is
an argument within VLOOKUP.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Ronald Dodge]

I rarely use the Offset worksheet function.

VLookup function uses the first column for it's list to look in and then
there's a column number that is entered as the third argument to return the
value of that particular column of the table.

The lookup function that I often times use the INDIRECT function with is the
MATCH function. Example is the following:

=INDIRECT(ADDRESS(MATCH(A7,Sheet1!$A:$A,0),COLUMN(),,,"Sheet1"))

The only thing you have to watch out for with the MATCH function is it
returns the Nth cell of either that column or row (can only be used with one
column or one row, can't be multiple columns and rows at the same time), so
if you use a range like $A$5:$A$250, if the data is found in A5, the MATCH
function will return the value of 1 cause A5 is the first cell within the
column. The above example is assuming though that both sheets are the same
structure column wise as you can see with the COLUMN() function, though not
necessarily the same rows.

 

[Dave Peterson]

You may want to use =index(match) instead.

Debra Dalgleish explains it:
http://www.contextures.com/xlFunctions03.html

You could use:

=index(sheet2!c:c,match(a1,sheet2!z:z,0))

to grab something from column C (of the same row) based on a match in column Z.

Or
=index(sheet2!c:c,match(a1,sheet2!z:z,0)+99)
to grab something from column c, but 99 rows after the match in column Z.