Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet "calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which will be used
for further calculations.

Thanks

Dan

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which will be
used
for further calculations.

Thanks

Dan

This works for the basic example but my cell is within another excel sheet.
It does not matter which method I use to locate the data, I need a means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my VLOOKUP or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns
an error.

Regards,

Dan


"Peo Sjoblom" wrote:

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which will be
used
for further calculations.

Thanks

Dan

It doesn't matter where the the table is, it will return the address with
workbook name and sheet name if it is in another sheet. I suspect that you
implement it incorrectly or that your data does not match (if you get #N/A).
Create a fake table and test and you'll see that it works

=CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0)))

will return the cell address in Sheet1

so either you applied it incorrectly or there is no exact matched lookup value


Regards,

Peo Sjoblom



"Dan" wrote:

This works for the basic example but my cell is within another excel sheet.
It does not matter which method I use to locate the data, I need a means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my VLOOKUP or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns
an error.

Regards,

Dan


"Peo Sjoblom" wrote:

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which will be
used
for further calculations.

Thanks

Dan

I retried the equation and it works for your example but it will not work
when the "reference" for CELL is a VLOOKUP.

I have VLOOKUP working in the cell directly above the CELL equation, then I
copied and inserted the equation into the CELL("address",VLOOKUP(...)) but I
get an error when I select enter.

Not sure where the error is or why it will not work with VLOOKUP.

Regards,

Dan

"Peo Sjoblom" wrote:

It doesn't matter where the the table is, it will return the address with
workbook name and sheet name if it is in another sheet. I suspect that you
implement it incorrectly or that your data does not match (if you get #N/A).
Create a fake table and test and you'll see that it works

=CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0)))

will return the cell address in Sheet1

so either you applied it incorrectly or there is no exact matched lookup value


Regards,

Peo Sjoblom



"Dan" wrote:

This works for the basic example but my cell is within another excel sheet.
It does not matter which method I use to locate the data, I need a means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my VLOOKUP or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It returns
an error.

Regards,

Dan


"Peo Sjoblom" wrote:

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The information is in
one excel sheet "data sheet" and the equation will be in another sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which will be
used
for further calculations.

Thanks

Dan

It's because you cannot use VLOOKUP to get this, you have to replace the
vlookup with the index match combination or else it will never work, in my
first example, to quote myself: "It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value"

VLOOKUP looks up a value in the leftmost column and then offset it by the
index number you use, that's the reason,
if you have a VLOOKUP formula in the cell above you still would need index,
assume that the vlookup value is 10 and it was looked up in Sheet1 column
F2:F50

=CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP( A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0)))

but that doesn't make any sense at all, it's much better to change the
vlookup to an index match



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
I retried the equation and it works for your example but it will not work
when the "reference" for CELL is a VLOOKUP.

I have VLOOKUP working in the cell directly above the CELL equation, then
I
copied and inserted the equation into the CELL("address",VLOOKUP(...)) but
I
get an error when I select enter.

Not sure where the error is or why it will not work with VLOOKUP.

Regards,

Dan

"Peo Sjoblom" wrote:

It doesn't matter where the the table is, it will return the address with
workbook name and sheet name if it is in another sheet. I suspect that
you
implement it incorrectly or that your data does not match (if you get
#N/A).
Create a fake table and test and you'll see that it works

=CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0)))

will return the cell address in Sheet1

so either you applied it incorrectly or there is no exact matched lookup
value


Regards,

Peo Sjoblom



"Dan" wrote:

This works for the basic example but my cell is within another excel
sheet.
It does not matter which method I use to locate the data, I need a
means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my VLOOKUP
or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It
returns
an error.

Regards,

Dan


"Peo Sjoblom" wrote:

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me
though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The
information is in
one excel sheet "data sheet" and the equation will be in another
sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based
on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which
will be
used
for further calculations.

Thanks

Dan

My apologies. I missed the statement about that VLOOKUP would not work within
the CELL call. I rearranged my equations to work with INDEX and all other
equations were modified to suit.

I appreciate all the assistance.

Regards,

Dan

"Peo Sjoblom" wrote:

It's because you cannot use VLOOKUP to get this, you have to replace the
vlookup with the index match combination or else it will never work, in my
first example, to quote myself: "It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value"

VLOOKUP looks up a value in the leftmost column and then offset it by the
index number you use, that's the reason,
if you have a VLOOKUP formula in the cell above you still would need index,
assume that the vlookup value is 10 and it was looked up in Sheet1 column
F2:F50

=CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP( A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0)))

but that doesn't make any sense at all, it's much better to change the
vlookup to an index match



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
I retried the equation and it works for your example but it will not work
when the "reference" for CELL is a VLOOKUP.

I have VLOOKUP working in the cell directly above the CELL equation, then
I
copied and inserted the equation into the CELL("address",VLOOKUP(...)) but
I
get an error when I select enter.

Not sure where the error is or why it will not work with VLOOKUP.

Regards,

Dan

"Peo Sjoblom" wrote:

It doesn't matter where the the table is, it will return the address with
workbook name and sheet name if it is in another sheet. I suspect that
you
implement it incorrectly or that your data does not match (if you get
#N/A).
Create a fake table and test and you'll see that it works

=CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0)))

will return the cell address in Sheet1

so either you applied it incorrectly or there is no exact matched lookup
value


Regards,

Peo Sjoblom



"Dan" wrote:

This works for the basic example but my cell is within another excel
sheet.
It does not matter which method I use to locate the data, I need a
means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my VLOOKUP
or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It
returns
an error.

Regards,

Dan


"Peo Sjoblom" wrote:

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me
though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The
information is in
one excel sheet "data sheet" and the equation will be in another
sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet" based
on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which
will be
used
for further calculations.

Thanks

Dan

Thanks for the feedback

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
My apologies. I missed the statement about that VLOOKUP would not work
within
the CELL call. I rearranged my equations to work with INDEX and all other
equations were modified to suit.

I appreciate all the assistance.

Regards,

Dan

"Peo Sjoblom" wrote:

It's because you cannot use VLOOKUP to get this, you have to replace the
vlookup with the index match combination or else it will never work, in
my
first example, to quote myself: "It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value"

VLOOKUP looks up a value in the leftmost column and then offset it by the
index number you use, that's the reason,
if you have a VLOOKUP formula in the cell above you still would need
index,
assume that the vlookup value is 10 and it was looked up in Sheet1 column
F2:F50

=CELL("address",INDEX(Sheet1!F2:F50,MATCH(VLOOKUP( A1,Sheet1!A2:F50,6,0),Sheet1!F2:F50,0)))

but that doesn't make any sense at all, it's much better to change the
vlookup to an index match



--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
I retried the equation and it works for your example but it will not
work
when the "reference" for CELL is a VLOOKUP.

I have VLOOKUP working in the cell directly above the CELL equation,
then
I
copied and inserted the equation into the CELL("address",VLOOKUP(...))
but
I
get an error when I select enter.

Not sure where the error is or why it will not work with VLOOKUP.

Regards,

Dan

"Peo Sjoblom" wrote:

It doesn't matter where the the table is, it will return the address
with
workbook name and sheet name if it is in another sheet. I suspect that
you
implement it incorrectly or that your data does not match (if you get
#N/A).
Create a fake table and test and you'll see that it works

=CELL("address",INDEX(Sheet1!B2:B7,MATCH(A1,Sheet1 !A2:A7,0)))

will return the cell address in Sheet1

so either you applied it incorrectly or there is no exact matched
lookup
value


Regards,

Peo Sjoblom



"Dan" wrote:

This works for the basic example but my cell is within another excel
sheet.
It does not matter which method I use to locate the data, I need a
means of
determining the position within the spreadsheet.

The CELL("address",reference) does not work when I intsert my
VLOOKUP
or
even your CELL("address",INDEX(array range,MATCH(A1,B2:B30,0))). It
returns
an error.

Regards,

Dan


"Peo Sjoblom" wrote:

It's better to get it like this, if

=VLOOKUP(A1,B2:C30,2,0)

returns a value from C2:C30

then

=INDEX(C2:C30,MATCH(A1,B2:B30,0))

will return the same value

now index can be used within the cell function

=CELL("address",INDEX(C2:C30,MATCH(A1,B2:B30,0)))

will return the cell address, why you want to do this is beyond me
though


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Dan" wrote in message
...
Want to determine the address of a given data point. The
information is in
one excel sheet "data sheet" and the equation will be in another
sheet
"calc
sheet". I used VLOOKUP to locate the data in the "data sheet"
based
on the
criteria in "calc sheet".

Now I need to determine the address of the returned data, which
will be
used
for further calculations.

Thanks

Dan