Return cell reference of lookup value
 

hello,

i am sure this is a pretty simple question but cannot work out.

i have two sheets - sheet1 and sheet2

in sheet1 i have names in Row 1 and dates in Column A

what i want to do from sheet2 is do a vlookup on a date in sheet1 and return
the cell reference (row number) of that lookup value.

and do the same with the name, do a hlookup on sheet1 and return the cell
reference (col number) of the lookup value.

This will then give me a range to plot to colour of the intersecting cell in
sheet2

i have tried using the lookup functions can only return the value, not the
cell reference. is there a better way to do this?

appreciate any help or other ways to do this.

bobm

hello some more information on the function i attempting to use...

=Cell("row",VLOOKUP(B2,sheet1!A:A,1,FALSE)) 'lookup date
=Cell("col",HLOOKUP(A2,sheet1!A:G,1,FALSE)) ' lookup name

but get alert that formula contains an error


"bobm" wrote:

hello,

i am sure this is a pretty simple question but cannot work out.

i have two sheets - sheet1 and sheet2

in sheet1 i have names in Row 1 and dates in Column A

what i want to do from sheet2 is do a vlookup on a date in sheet1 and return
the cell reference (row number) of that lookup value.

and do the same with the name, do a hlookup on sheet1 and return the cell
reference (col number) of the lookup value.

This will then give me a range to plot to colour of the intersecting cell in
sheet2

i have tried using the lookup functions can only return the value, not the
cell reference. is there a better way to do this?

appreciate any help or other ways to do this.

bobm

Assuming the following:
1. Your date list is named "DateRange"
2. Your name list is named "NameRange"
3. Your date to lookup is in a cell named "DateToMatch"
4. Your name to lookup is in a cell named "NameToMatch"
5. Your data is laid out as you describe in your question

Then this formula should do the trick ...

=ADDRESS(MATCH(DateToMatch,DateRange,0)+1,MATCH(Na meToMatch,NameRange,0)+1)

Rgds,
ScottO

"bobm" wrote in message
...
| hello some more information on the function i attempting to use...
|
| =Cell("row",VLOOKUP(B2,sheet1!A:A,1,FALSE)) 'lookup date
| =Cell("col",HLOOKUP(A2,sheet1!A:G,1,FALSE)) ' lookup name
|
| but get alert that formula contains an error
|
|
| "bobm" wrote:
|
| hello,
|
| i am sure this is a pretty simple question but cannot work out.
|
| i have two sheets - sheet1 and sheet2
|
| in sheet1 i have names in Row 1 and dates in Column A
|
| what i want to do from sheet2 is do a vlookup on a date in sheet1 and
return
| the cell reference (row number) of that lookup value.
|
| and do the same with the name, do a hlookup on sheet1 and return the
cell
| reference (col number) of the lookup value.
|
| This will then give me a range to plot to colour of the intersecting
cell in
| sheet2
|
| i have tried using the lookup functions can only return the value, not
the
| cell reference. is there a better way to do this?
|
| appreciate any help or other ways to do this.
|
| bobm
|

Thanks, brilliant!