Formula for: Compare value of one cell to range of 5 cells?

[photoguy53]

Can anyone show me how to write a formula to compare the value of cell to the values in 5 different cells, and to return the value of the cell that the orginal cell matches?

What I'm trying to do is like this:

Does the value of C10 = C7, D7, E7, F7, G7
if it matches any of those cells, then return that value to my target cell.

Thanks!

 

[Brian from Maui]

Re: Formula for: Compare value of one cell to range of 5 cel

Can anyone show me how to write a formula to compare the value of cell to the values in 5 different cells, and to return the value of the cell that the orginal cell matches?

What I'm trying to do is like this:

Does the value of C10 = C7, D7, E7, F7, G7
if it matches any of those cells, then return that value to my target cell.

Thanks!

=INDEX(C7:G7,MATCH(C10,C7:G7,0))

Will return #N/A if no match found

 

[photoguy53]

Re: Formula for: Compare value of one cell to range of 5 cel

Thanks!! I'll give that a try!

 

[DRJ]

If you don't want the #N/A then

=If(ISNA(INDEX(C7:G7,MATCH(C10,C7:G7,0))),"No Match",C10)

 

[photoguy53]

Re: Formula for: Compare value of one cell to range of 5 cel

Both formulas worked! Thanks!!
I'll use DRJ's , since it's a little more elegant a solution.
Thanks to both!!

 

[DRJ]

You're Welcome

Take Care

 

[kakha]

Does this work without index. I wrote with and without index and i got same value. Formulas: =MATCH(A3,$F$3:$F$7,0) || =INDEX($A$3:$A$7,B3) || =INDEX($F$3:$F$7,MATCH(A3,$F$3:$F$7,0))

value only match only index index & match array 1 1 1 1 1 2 2 2 2 2 7 #N/A #N/A #N/A 3 9 #N/A #N/A #N/A 4 5 5 5 5 5 <colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>