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

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Roccobarocco" wrote in message
...
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

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" wrote:

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Roccobarocco" wrote in message
...
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

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))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Roccobarocco" wrote in message
...
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?

What if it is NOT sorted in ascending order?

"Max" wrote:

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))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Roccobarocco" wrote in message
...
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?

"Roccobarocco" wrote
What if it is NOT sorted in ascending order?

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

"Frank Kabel" wrote

Hi
for the closest value in an unsorted list use the following array
formula (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A5,MATCH(MIN(ABS(A1:A5-C1)),ABS(A1:A5-C1),0))

--
Regards
Frank Kabel
Frankfurt, Germany

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----