Finding a Cell refrence... 2

[scotsql]

Hi all,

I want a formula which will tell me the cell reference in a range if it equals another cell.

But this just gives me FALSE

=IF(A1:A106=K1,CELL("address",A1:A106))

i hope i have explained this well enough.

thanks for any help..

 

[SkipVought]

No, it is not clear.

1.
[tt]
=IF(A1:A106=K1,CELL("address",A1:A106))
[/tt]
the bold part is not correct

2. the form of an IF statement is
[tt]
=IF(<expression>,<Result if TRUE>,<Result if FALSE>)
[/tt]
3. CELL("address",A1:A106) returns $A$1

so I have not idea what you are trying to do.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[DaleWatson123321]

One method...

=VLOOKUP(K1,A1:B106,2,FALSE)

...where =CELL("address",A1)
is entered into B1 and copied to B1:B106

(You could insert and hide column B if necessary.)

Hope this helps.

Regards, Dale Watson

 

[GlennUK]

Another method ...

=CELL("address",OFFSET(A1,MATCH(K1,A1:A106,0)-1,0))



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[DaleWatson123321]

Glenn,

Yours is obviously better ;-) ...and worthy of a STAR.

Regards, Dale Watson

 

[GlennUK]

Thank you

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.