Lookup in a range and find a value in a range

[xcel87]

Hello Friends,[TABLE="width: 1020"]
<colgroup><col><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD="colspan: 13"]I am trying to find if any of the values in a range cell matches to any value in another another range of cell.One cell range has fixed value another cell range keeps changing constantly.
I need to write a formula in such a way that anytime any value changes in range of cell and if the value is in other range of cell

Any idea how to set up this formula

Thanks[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try to specify the ranges and the outcome you want.

 

[xcel87]

I have 5 values in cell A1:A5(20,78,52,65,12). I am trying find if any of this values matches in cell B1:B5(This range starts with empty cells and depends on user input).If a user enters 20 on cell B5 and that matches the value in cell A1 i need to conditionally format cell A1. I am trying to figure out how can we do that.

Thanks

 

[Nishant94]

Select A1:A5 with A1 being the active cell. Then go to conditional formatting in home tab or (Alt+O+D)
Then press Alt+N.
Choose the formula option and put this formula:

=ISNUMBER(MATCH(A1,$B$1:$B$5,0))

 

[xcel87]

That worked perfectly. Thank you Nishant