Formating a range from two columns inside a countif formula

Question

Disclaimer I am trying to keep this is simple for the user. So they just paste in names in the format they are given (A, B) and it continuously highlights (C) without formatting (B,A) first.

If I have a spreadsheet that has First Name in Column A and Last Name in Column B. In column C are the corresponding names I'm highlighting with a custom conditional formatting rule. Column C has the names formatted in lastName, firstName.

My question is can I format column A and B in to $B&", "&$A in a countif formula?

This does not seem to work =countif($B&", ":$A,$C)

whereas $B&", ":$A would format into lastName, firstName and thus trigger the rule to highlight the same lastName, firstName in C?

Then the user just comes in and pastes more and more names and column c keeps updating.

Answer 1

You can use a conditional formatting rule with a formula along these lines:

=ISNUMBER(MATCH(C1,INDEX($B$1:$B$10&", "&$A$1:$A$10,0),0))

The Index will concatenate the cells in column B with a comma, a space and the cells in column A. That is then used as input for Match.

You don't want to use whole column references for this. Refer to concrete row numbers or use an Excel Table and refer to the column names. Otherwise this will make the workbook very slow, since the conditional format is volatile and the formula takes a while to calculate on a large dataset.