Using a Variable for Reference in Offset

[ExcelJeff]

How can I use a variable for the reference of an Offset lookup?

I want users to select the metric they want data from by using a dropdown. The results of the dropdown should then drive the reference field in the offset function.

Something like this:
=OFFSET(MyVariable, 1,1)

Thanks in advance for any help.

 

[T. Valko]

How can I use a variable for the reference of an Offset lookup?

I want users to select the metric they want data from by using a dropdown. The results of the dropdown should then drive the reference field in the offset function.

Something like this:
=OFFSET(MyVariable, 1,1)

Thanks in advance for any help.

You could use something like this:

=OFFSET(INDEX(A:A,MATCH(MyVariable,A:A,0)),1,1)

 

[ExcelJeff]

You could use something like this:

=OFFSET(INDEX(A:A,MATCH(MyVariable,A:A,0)),1,1)

Doesn't seem to be working.

 

[T. Valko]

Doesn't seem to be working.

Ok, how about providing some details!

Like, what exactly is "MyVariable"?

What kind of drop down list are you using?

Where is it located?

Where do we look to find "MyVariable"?

There may be more questions but we'll go with those as a starting point.

 

[ExcelJeff]

Ok, how about providing some details!

Like, what exactly is "MyVariable"?

What kind of drop down list are you using?

Where is it located?

Where do we look to find "MyVariable"?

There may be more questions but we'll go with those as a starting point.

I am using a basic drop down list that currently has two values: NaturalSearch and PaidSearch. I have a cell on the same worksheet that populates with the text as selected from the drop down. So cell R2 changes to read either NaturalSearch or PaidSearch depending on what is selected in the dropdown.

I have an offset function that does a lookup using NaturalSearch as the reference. The current funtcion is:
=OFFSET(NaturalSearch,1,0,1,1)

If the user selects PaidSearch from the drop down I would like that offset function to use PaidSearch instead on NaturalSearch as the reference in the offset function.

I hope that helps....and I greatly appreciate your assistance!!! Thanks!

 

[T. Valko]

I am using a basic drop down list that currently has two values: NaturalSearch and PaidSearch. I have a cell on the same worksheet that populates with the text as selected from the drop down. So cell R2 changes to read either NaturalSearch or PaidSearch depending on what is selected in the dropdown.

I have an offset function that does a lookup using NaturalSearch as the reference. The current funtcion is:
=OFFSET(NaturalSearch,1,0,1,1)

If the user selects PaidSearch from the drop down I would like that offset function to use PaidSearch instead on NaturalSearch as the reference in the offset function.

I hope that helps....and I greatly appreciate your assistance!!! Thanks!

Ok, NaturalSearch and PaidSearch have to evaluate to a reference to be used in the OFFSET function. If they are simply text strings then what you're trying:

=OFFSET(NaturalSearch,1,0,1,1)

won't work.

So, we need to know what exactly NaturalSearch and PaidSearch refer to and where we can find them (other than when selected from the drop down list).

 

[ExcelJeff]

Ok, NaturalSearch and PaidSearch have to evaluate to a refernece to be used in the OFFSET function. If they are simply text strings then what you're trying:

=OFFSET(NaturalSearch,1,0,1,1)

won't work.

So, we need to know what exactly NaturalSearch and PaidSearch refer to and where we can find them (other than when selected from the drop down list).

=OFFSET(NaturalSearch,1,0,1,1) currently works and returns the value I am looking for.

NaturalSearch and PaidSearch are names for tables of data contained on a separate worksheet. The worksheet name is Keywords.
PaidSearch is the name for C1 to G51
NaturalSearch is the name for I1 to K51

 

[T. Valko]

=OFFSET(NaturalSearch,1,0,1,1) currently works and returns the value I am looking for.

NaturalSearch and PaidSearch are names for tables of data contained on a separate worksheet. The worksheet name is Keywords.
PaidSearch is the name for C1 to G51
NaturalSearch is the name for I1 to K51

Ok, we're making progress!

Typically, we'd look in the first column of a table to find some lookup value and then offset some number of rows/columns to find what we're actually looking for.

So, if you want to look in the PaidSearch table what exactly are we looking for?

 

[ExcelJeff]

Ok, we're making progress!

Typically, we'd look in the first column of a table to find some lookup value and then offset some number of rows/columns to find what we're actually looking for.

So, if you want to look in the PaidSearch table what exactly are we looking for?

The OFFSET function I have setup now, works and returns what I am looking for, the keyword, searches and revenue. It is part of a dashboard and I would like the table to generate the content dynamically, based on what a user wants to see. So if the user selects paid search or natural search from the drop down, the data will change accordingly.

The function works 100% as either
=OFFSET(NaturalSearch,1,0,1,1)
=OFFSET(PaidSearch,1,0,1,1)

What I want to do is have the reference in the OFFSET change based on what is selected in a dropdown.

 

[Jonmo1]

Try

=OFFSET(INDIRECT(A1),1,0,1,1)

A1 = your cell that contains either NatrualSearch or PaidSearch derived from your dropdown.