Comparing cell values in Excel

Question

What I am trying to achieve is to see if the value of Cell C4 exists in Column F.

If it exists in Column F, I eventually want Cell A4 to copy the format of the cell in Column F that matches the value of Cell C4.

Still following me?

For reference, my file looks like this:

The values of the cells in Column A are based on Sheet2.

So with a simple formula I managed to compare the values and only change Column A when there is an actual match as seen below.

=IF(C4=F:F, IF(C4<>"","x",Sheet2!A4), Sheet2!A4)

The only problem here is that it only results in a TRUE statement, if Cell C4 matches Cell F4. If it matches, let's say Cell F5, the statement will be FALSE and I need it to be TRUE (since value of C4 is indeed found in Column F.

I tried a variation using $F:$F instead of F:F, but this made no difference. Also setting a range (F2:F5) did not work.

This is where I am stuck.

As soon as I have this figured out, I can continue finding a way of copying the format of the matched cell. Feel free to reveal how I manage to do this if you already know. Will save me some headaches.

Answer 1

Try the following:

=IF(IFERROR(MATCH(C4,F:F,0),0)>0,IF(C4<>"","x",Sheet2!A4), Sheet2!A4)

Edited formula error (; --> ,)

EDIT:

Ok, so forgetting about you formula and trying to do what I think you want, which is:

Be able to copy in the list of resolvers at work into your color code scheme, and then apply that format to all tickets where this person is responsible. There has to be a way of telling, based on color, whether a ticket is assigned to a person that is not on the job (not in the list), or is not assigned to anyone. I assume you have a very limited amount of people at work, because if not, it will be very difficult to distinguish between the colors.

Being quite few people at work at one time, what you could do is to:

1. Name your the range with people at work "workers"
2. Name the new range "colors"
3. For each row with a color coding, enter the name of the color, or some other way of referencing the color. I entered "Yellow" "Red", "Green", "Blue", and I added a black for "x".

4. Selected the top cell in the tickets column, selected new conditional formatting rule, entered the following formula

   =IFERROR(INDEX(colors;MATCH($C1;workers;0));"x")="Yellow"
   

   Selected that this should have yellow fill, and applied to the entire Tickets column.

5. Did the same for red, green, black, etc.

Doing this, I managed to get the following behaviour: 1. I can change the names in the name column, and conditional formatting will not change

2. If a ticket is solved by someone in the list, the background color of TicketX is the same as the name in the name list

3. If a ticket is not solved by anyone, the formula returns "", which is not in any of the conditional formatting rules, so it remains white

4. If a ticket is solved by someone not at work, formula evaluates to "x", which is tied to color black in conditional formatting, so the cell turns black (could consider adding white text here to be able to see ticket number).

Now this is not that pretty, because you have to create so many rules, but as I say, if you are to distinguish between the colors, there can't be too many of them anyway.

Answer 2

To answer the first question of seeing if C4 exists in column F, you can put the following formula in cell E4.

=IFERROR(INDEX($F$2:$F$5,MATCH(C4,$F$2:$F$5)),"")

It will put the name if it exists and have a blank field if it doesn't. Just drag this formula throughput column E (or whatever column you want).

In order to match formats and don't want to get into VBA (and assuming you don't have a lot of possibilities), you could add conditional formatting to the new column of E. With the "Classic" Style of conditional formatting, make a new rule that sets your custom format when the cell contains the specific text of "Henk". Then continue on making rules for the other possible options. After you create the conditional formatting in, say, cell E4 for example, change the "Applies to" field to apply to Sheet1!$C$2:$C$5.