Return cell reference of lookup value

[Guest]

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

 

[Guest]

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

 

[ScottO]

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(NameToMatch,NameRange,0)+1)

Rgds,
ScottO

| 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
| >

 

[Guest]

Thanks, brilliant!