Referring to cells in a range

Morning Roger

Yes, you can find a value in a range without using VBA by using the INDEX function.

In any cell of your workbook containing a range called TestRange try typing:

=INDEX(TestRange,1,1)

The Index function returns the value at the position row 1, col 1 within the range TestRange.

Hope that helps.

Other example of Index
Index is sometimes used to find a value if a position in a range in known (throught the Match function)

A B
John 250
Sue 400
Rich 120
Val 300

Example
To find the person with the highest value without sorting type:

=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0),1)

The Match function finds the postion 2 for Max value in B1:B4 and the Index function returns the value in that row of the range A1:A4 which is Sue!

Regards
Doug

Doug Dunn
Best STL