Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset & Indirect Function
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 -- Helping Is always a good thing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset & Indirect Function
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) "QuietMan" wrote in message ... 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 -- Helping Is always a good thing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset & Indirect Function
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. -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 "QuietMan" wrote in message ... 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 -- Helping Is always a good thing |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset & Indirect Function
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. QuietMan wrote: 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 -- Helping Is always a good thing -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset/Indirect Help | Excel Worksheet Functions | |||
INDIRECT, OFFSET et. al. | Excel Worksheet Functions | |||
offset and indirect function | Excel Worksheet Functions | |||
offset and indirect function | Excel Worksheet Functions | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |