MATCH query too exact!

  • Thread starter Thread starter ianripping
  • Start date Start date
I

ianripping

I have cells like these

Ian Ripping
Joe Bloggs
Harry Bling
etc

I use the match query to find names (=match("ian",a1:a1000))

Problem is, my match has to be exact.

Eg if I search for Ian Ripping, I will get the correct row number - 1
If I search for Ian, I get #n/a!

Is there a way of searching within the cells to find a name match
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=MATCH(TRUE,ISNUMBER(FIND("Joe",A1:A1000)))
 
No sorry I just either get the last number in the range or a #N/A
message. Nice try thoug
 
Try this slight tweak on Frank's formula which will give you the row number it
is on

=MATCH(TRUE,ISNUMBER(FIND("jim",A2:A25)),0)+ROW(A2)-1

or as it was with a slight tweak, but returning the position in the range

=MATCH(TRUE,ISNUMBER(FIND("jim",A2:A25)),0)

Both still array entered
 
Hi
is your range starting in row 1 (thats is for example A1:A100). If yes
the formula should work (you have to array enter it!).
 
Back
Top