IF and CountIFS - Invalid Column value

Options

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

Answers

  • Nate Ensor
    Nate Ensor ✭✭✭✭✭
    Options

    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
    L_123 ✭✭✭✭✭✭
    Options

    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
    Nate Ensor ✭✭✭✭✭
    Options

    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
    L_123 ✭✭✭✭✭✭
    Options

    Can you post the formula?

  • Nate Ensor
    Nate Ensor ✭✭✭✭✭
    Options

    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
    L_123 ✭✭✭✭✭✭
    Options

    NP glad you got it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!