Update a cell from comparing cells in a list

scrfix

New Member
Joined
Sep 5, 2010
Messages
32
I have three columns; Column A, B and C.

1. Column A. This is a blank column that compares what is entered into Column B to a list of filled in cells in Column C. If it finds a match, it puts a Y. If it does not find a match it puts an N.

2. Column B. This is where a user enters data or leaves it blank if the data is unknown.

3. Column C. This contains a prefilled in list of 250 items.
$C$2 - $C$251

Goal:
When someone enters something into $B$2, cell $A2 looks at what is entered in $B$2 and looks for a match against the list in cells $C$2 - $C$251. If it finds a match, a Y goes into cell $A2 and it turns yellow or whatever conditional formatting. If it does not find a match it puts a N. If cell $B$2 remains blank then cell $A2 will remain blank.

Can anyone help with this? I have been attempting a few things and just cannot get this to work.

Thanks for any help. I would prefer not to utilize a macro if possible.

Something to the effect of:

Code:
=IF($B2>0,"",MATCH($B2,$C2:$C251,0))
But that just gives me the row number it was matched to. I am looking to update the cell with it.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Try...

A2:

=IF(B2="","",IF(ISNUMBER(MATCH(B2,$C$2:$C$251,0)),"Y","N"))

In conditional formatting, test A-cells for being equal to ="Y".
 

scrfix

New Member
Joined
Sep 5, 2010
Messages
32
That works fantastic! Thank you. I was killing myself trying to come up with something.
 

Forum statistics

Threads
1,085,364
Messages
5,383,199
Members
401,818
Latest member
MrMisster

Some videos you may like

This Week's Hot Topics

Top