IF and CountIFS - Invalid Column value
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.
Best Answer
-
=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.
Answers
-
=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.
-
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?
-
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.
-
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?
-
Can you post the formula?
-
Just got it. Was missing a comma. Thanks for your help!
=JOIN(COLLECT({Return column}, {criteria 1}, [Column5]@row, {Criteria 2}, [Column6]@row),", ")
-
NP glad you got it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 440 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!