using match or other

[Frank Bacchus]

All :

I have a spreadsheet with column A thru D with 300 rows In column D there text in that may be found only in column A of the spreadsheet. I used the MATCH Function but it doesn't seem to work. Here is what I have:

=MATCH(D2,A2:A268,0) and the results is #N/A. The value in D2 is in Row 161 as part of the A161 cell string. What am I missing? Thanks

frank

 

[Hui]

=MATCH("*"&D2&"*",A2:A268,0)

 

[Frank Bacchus]

=MATCH("*"&D2&"*",A2:A268,0)

Hui:

Thank you. I am just a tiny bit confused. I needed to change the formula to :
=MATCH("*"&S2&"*",$A$2:$A$268,0), so that the entire lookup array is reviewed in each row. The result displays a number and I am assuming that this number represents the row number where the searched text was found. What I am finding, is that the content in that row does not have the search text value but it appears in prior or subsequent rows. Is there an explanation for this ? Thanks.

frank

 

[bosco_yip]

Hui:

Thank you. I am just a tiny bit confused. I needed to change the formula to :
=MATCH("*"&S2&"*",$A$2:$A$268,0), so that the entire lookup array is reviewed in each row. The result displays a number and I am assuming that this number represents the row number where the searched text was found. What I am finding, is that the content in that row does not have the search text value but it appears in prior or subsequent rows. Is there an explanation for this ? Thanks.

frank

If result number represents the row number, formula should be :

=MATCH("*"&S2&"*",$A$1:$A$268,0)

Regards

 

[Hui]

Match returns the Index No of the Matching item within the array
So in your example
=MATCH("*"&S2&"*",$A$2:$A$268,0)

if the match occurs in Row 5 that is the 4th Line of the Array which starts at A2

In Bosco's Formula:

=MATCH("*"&S2&"*",$A$1:$A$268,0)

if the match occurs in Row 5 that is the 5th Line of the Array which now starts at A1 and hence it returns an Index No. that coincides with the Row Number, But it should not be confused with the Row Number