Excel concatenate with IF statement

Question

What I am trying to do is concatenate two cells, then check that concatenated value against a column of values, and put an X in a cell if such a value exists. The following equation is what I'm using:

{=IF(CONCATENATE(A2, " ", B2) = 'MasterList'!$C:$C, "x", "")}

Column A is the name of the software and Column B the version number (A="Microsoft .NET Framework 4.5.1", B="4.5.50938", for example). I know that this concatenated value exists on the MasterList worksheet so I don't understand why I'm not getting the answer I expect.

Gurus, what's my flaw here?

Solution 1

I just want to say that before seeing this question, I had no idea what array formulas were.

I have the following working example:

Sheet1:

A1: Microsoft .NET Framework 4.5.1
B1: 4.5.50938

// press control-shift-enter after typing formula
C4: =IF(CONCATENATE(A1," ",B1)=Sheet2!$A:$A,"YAY","NAY")

Sheet2:

A1: Microsoft .NET Framework 4.5.1 4.5.50938

Not sure what the issue is with your spreadsheet. Do the values in MasterList!$C:$C actually correspond to what you expect as the result of the concatenation?

Solution 2

This is not a valid array formula. The LHS is a single cell while the RHS is an array. What it actually does is compare the concatenation to all cells of columns C in MasterList.

You either need to make it a valid array formula or a normal formula. I recommend the second solution.

For the first solution, it should be:

{=IF(CONCATENATE(A:A, " ",B:B ) ='MasterList'!$C:$C, "x", "")}

However, this will be very slow because it will compute and concatenate two full columns, and moreover you will have to select your whole range where you want to calculate it (a full columns) then press Ctrl+Shift+Enter.

You can make it a simple formula in on cell then copy/paste along your column. Formula for C2:

=IF(CONCATENATE(A2, " ", B2) = 'MasterList'!$C2, "x", "")

Solution 3

It seams that you select the entire column C on yout MasterListSheet. Don't you mean just one cell? Like:

=IF(CONCATENATE(A2, " ", B2) = 'MasterList'!$C1, "x", "")

or try to : =IF(CONCATENATE(A2, " ", B2) = MasterList.!$C1, "x", "") (on open Office)

Best thing is instead of you write the Sheet name, let Excel do it for you by editing the formula, remove the sheet name and then with a mouse click navigate to the desired area.

Comments

[Tchotchke]

Truthfully I'm not sure why it wasn't equating like your example. That's exactly what I was trying before I thought I needed an array formula (and I need to read-up, again, on them).