IF and CountIFS - Invalid Column value

Nate Ensor

Hi -

Attempting to use IF and CountIFS to bring back info from another referenced sheet.

Using this formula I am able to return the specified text:

=IF(COUNTIFS({Sheet 2 reference column Range 1}, [Column1]@row, {Sheet 2 column range 2}, [Column2]@row) > 0, "blah")

Using this formula I receive the "invalid column value":

=IF(COUNTIFS({Sheet 2 reference column Range 1}, [Column1]@row, {Sheet 2 column range 2}, [Column2]@row) > 0, {Sheet 2 column Range 3})

So the difference is that I'm trying to return the value in a 3rd column vs just a specified text when a match is found. Is there a way to do this via the IF and countIFS formula? I am able to return the correct value via the vlookup function, but due to the size of the sheets, I have to create multiple keys for each new column value i want to return.

L_123

=join(collect({Sheet 2 column Range 3},{Sheet 2 reference column Range 1}, [Column1]@row, {Sheet 2 column range 2}, [Column2]@row),", ")

give that a try. The reason you are getting an error is the last reference in your if statement, only true or fast can come out of an if criteria, so the criteria you built is lost once the program reaches that range.

*Also you should name your ranges. it becomes important after the sheet has been running awhile and you either have to fix it because it broke or want to improve/edit it.

L_123

=join(collect({Sheet 2 column Range 3},{Sheet 2 reference column Range 1}, [Column1]@row, {Sheet 2 column range 2}, [Column2]@row),", ")

give that a try. The reason you are getting an error is the last reference in your if statement, only true or fast can come out of an if criteria, so the criteria you built is lost once the program reaches that range.

*Also you should name your ranges. it becomes important after the sheet has been running awhile and you either have to fix it because it broke or want to improve/edit it.

Nate Ensor

ok that makes sense about the true/false, thanks. I get unparseable for your suggested function. I'm not sure if I'm connecting the right ranges. what does range 3 represent? How does range 3 and range 1 match a single column 1 on sheet 1? But then range 2 only matches column 2 on sheet 1?

L_123

range 3 is your return column

range 1 is your first criteria column, criteria is that is matches column1 in the same sheet and same row

range 2 is your second criteria column, criteria is that it matches column2 in the same sheet and same row.

Nate Ensor

ok got it. I've got all that filled out correctly now. Still getting unparseable though. If I'm referencing range 1 from another sheet...the criteria is that it matches column1 which is in the current sheet/row? The sheet I'm creating the formula in?

If that is true then I have it set up correctly - so maybe I need to adjust the delimeter of the Join function? I have it exactly as you put it, but you may have assumed I would replace with something?

L_123

Can you post the formula?

Nate Ensor

Just got it. Was missing a comma. Thanks for your help!

=JOIN(COLLECT({Return column}, {criteria 1}, [Column5]@row, {Criteria 2}, [Column6]@row),", ")

L_123

NP glad you got it.