search functions

is it possible to search for a value in a range of cells rather than just in a string of text. Any help would be great.

Thanx
Iain

Your intent is not clear. Can you elaborate? It would help if you would provide a small sample of the data, along with the expected result.

sid energymj natural vitb12 ug/d enadj tp1 sid energymj natural vitb12 enadj tp2
4039 5.7 5.2 0.91 1051 5.96 16.8 2.83
1039 7.27 5.7 0.78 1135 9.2 11.7 1.27
2029 5.46 4.0 0.74 1119 7.43 9.2 1.23
2102 4.29 2.6 0.60 2075 6.27 7.5 1.20
2103 8.03 4.6 0.58 4099 9.16 10.0 1.09
2078 4.82 2.7 0.56 4014 6.74 6.0 0.88
4056 7.86 4.3 0.55 2110 8.38 6.9 0.82
1119 9.38 4.9 0.52 1075 8.82 6.8 0.77
2077 8.45 4.3 0.50 1018 5.36 4.1 0.77
4099 9 4.5 0.50 2102 5.33 4.0 0.74
4012 9.67 4.7 0.49 4062 8.61 6.2 0.72
4014 6.68 3.3 0.49 2001 7.08 5.1 0.72
4068 12 5.8 0.48 4101 7.61 5.4 0.71
2075 6.37 3.0 0.48 2078 5.63 4.0 0.71
1099 6.8 3.2 0.47 2052 9.09 6.5 0.71
1018 8.32 3.6 0.43 1090 6.21 4.4 0.71
4062 10.92 4.5 0.41 1032 4.58 3.2 0.71
2001 7.98 3.3 0.41 2043 6.23 4.4 0.70
4088 12.09 4.9 0.41 1094 7.68 5.3 0.69


I want to find if A2 ("4039") is present in the cell range F2 to F20. this is only a small protion of the data.

Thanks for ur help

iain

Is the data contained in one column or are they contained in separate columns? Also, once A2 is found in F2:F20, what would you like the formula to return?

a simple logical return would be grand 1 or 0. The idea is to be able to fill down and acertain if each of the values in column A is present in column F. The column to be searched will always be column F.

Thanks

iain

If A2 occurs at the beginning of the text string for any cell in F2:F20, try...

=ISNUMBER(MATCH(A2&"*",$F$2:$F$20,0))

If A2 can occur anywhere within the text string, try...

=ISNUMBER(MATCH("*"&A2&"*",$F$2:$F$20,0))

Note that both these formulas return TRUE or FALSE. If you'd like the formula to return 1 or 0, add...

+0

...at the end of the formula.

Hope this helps!