any lookup question

[AlanY]

what is the best way to locate "test" within the range A1:J10, say.

without VBA preferably.

many thanks in advance.

[Punnam]

what exactly you are trying to do ,

=if(countif(a1:j10,"test")>=1,"Exists","No")
If you want to count

Please Login or Register  to view this content.

[AlanY]

what exactly you are trying to do ,

=if(countif(a1:j10,"test")>=1,"Exists","No")
If you want to count

Please Login or Register  to view this content.

i need the cell address of "test".

[AlanY]

you can see i'm very new here.

how to one change the thread title? it was meant to be "another lookup question".

[AlanY]

deleted.
doubled post

[Sam Capricci]

you could try this formula =ADDRESS(MATCH(INDEX($A$2:$A$1004,SMALL(IF(NOT(ISERROR(SEARCH("test",$A$2:$A$1004,1)>0)),ROW($A$2:$A$1004)-ROW($A$2)+1),ROWS(A$2:A2))),$A$1:$A$1004,0),1) which is an array formula so after entering it into a cell, you hit control, shift and enter all at the same time.
as for changing the thread title, i think you do it using the thread tools at the top of the post.

[AlanY]

you could try this formula =ADDRESS(MATCH(INDEX($A$2:$A$1004,SMALL(IF(NOT(ISERROR(SEARCH("test",$A$2:$A$1004,1)>0)),ROW($A$2:$A$1004)-ROW($A$2)+1),ROWS(A$2:A2))),$A$1:$A$1004,0),1) which is an array formula so after entering it into a cell, you hit control, shift and enter all at the same time.
as for changing the thread title, i think you do it using the thread tools at the top of the post.

thanks, got #NUM error.
that only work if "test" is in column A, how to I extend the range to A1:J10 please?

[twiggywales]

try somthing like this

Please Login or Register  to view this content.

A1:K17 can be any range you like just change it in all areas

[AlanY]

try somthing like this

Please Login or Register  to view this content.

A1:K17 can be any range you like just change it in all areas

many thanks, that work!