which formula or function searches for a value in a range of cell.

[Guest]

Hi

Does anyone know which function/formula to use to return a specific value
from a range of cells? For example:

A B C D


1 10
2 20
3 30
4 20
5 22
6 ?


How do I make A6 return the first instance of 20 from range A1:A5?

If this is not clear, just let me know. All help greatly appreciated

 

[Max]

One way ..

Put in B1:

=INDEX(A:A,MATCH(C1,A:A,0))

Enter the value to be returned in C1

The "0" or FALSE arg in MATCH (..)
will always return only the 1st instance
of the match found for the value in C1
within col A

 

[Guest]

Ok, this works well if the exact value is in the range. What if the exact
value is not in the range and you need to return the first instance closest
to your value?

 

[Max]

Then, provided the list in col A is *sorted*
in ascending order, you could use
a "1" or "TRUE" in the 3rd arg in MATCH()
instead (or just omit the 3rd arg altogether)

=INDEX(A:A,MATCH(C1,A:A,1))

 

[Guest]

What if it is NOT sorted in ascending order?

Then, provided the list in col A is *sorted*
in ascending order, you could use
a "1" or "TRUE" in the 3rd arg in MATCH()
instead (or just omit the 3rd arg altogether)

=INDEX(A:A,MATCH(C1,A:A,1))

 

[Max]

What if it is NOT sorted in ascending order?

Just in case you did not receive,
Frank has posted the suggestion below ..