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))
[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]value[/TD]
[TD="class: xl65, width: 64"]only match[/TD]
[TD="class: xl65, width: 64"]only index[/TD]
[TD="class: xl65, width: 64"]index & match[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]array[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD="class: xl65, align: center"]#N/A[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]