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?

user4039065 · Accepted Answer · 2017-03-29 23:04:07Z

2

The fastest comparison/lookup on the worksheet is a MATCH function. If you have a long column to put this formula into you could try,

=IF(ISNUMBER(MATCH(CONCATENATE(A2, " ", B2), 'MasterList'!$C:$C, 0)), "x", "")

Fill down as necessary.

answered Mar 29, 2017 at 23:04

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Tchotchke Over a year ago

Superb! I knew there was a better way to go about it! Thanks for the assist Jeeped!

Alg_D · Accepted Answer · 2017-03-29 22:41:18Z

1

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.

answered Mar 29, 2017 at 22:41

Alg_D

2,4307 gold badges37 silver badges68 bronze badges

Comments

Forest Kunecke · Accepted Answer · 2017-03-29 22:46:18Z

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?

edited Mar 29, 2017 at 22:46

answered Mar 29, 2017 at 22:30

Forest Kunecke

2,15015 silver badges33 bronze badges

1 Comment

Tchotchke Over a year ago

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).

A.S.H · Accepted Answer · 2017-03-30 05:17:19Z

1

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", "")

edited Mar 30, 2017 at 5:17

answered Mar 29, 2017 at 22:39

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Collectives™ on Stack Overflow

Excel concatenate with IF statement

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related