IF & VLOOKUP Formula Only Returning First Value Found

J.H. Lim
J.H. Lim ✭
in Formulas and Functions

Hello, everyone.

I'm having a problem where I'd like the formula in [Column 3] to report the value in [Primary Column] only if the checkbox in [Column 2] is TRUE (checked). (In the real sheets, the VLOOKUP formula in [Column3] is on another sheet from the values in [Primary Column]:[Column2] here, otherwise I'm sure it's much more straightforward to reference the actual cells themselves.)

However, it looks like formula will only pull the first value from the top that it comes across that matches its criteria. In the example photo, the second "A" in [Primary Column] isn't being reported, instead displaying "woof" (IF formula found a false statement).

image.png
image.png

Ideally, when the VLOOKUP sees that the first "A" value in [Primary Column] is unchecked (false), the formula should move on and find the next instance where "A" appears AND [Column2] is checked to report that instead.

Is there a way to get that to work?

· Share on FacebookShare on Twitter

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @J.H. Lim

    VLOOKUP will always return the first result found which is why unique values are used so only one answer can be found for each search. Cases like this one can be solved by introducing unique values and assigning them to the rows. Most people just start with 1 and work there way up from there.

    If that isn't possible because you don't have anyway to edit the main sheet you can try creating unique values through searching for multiple criteria with INDEX/COLLECT

    =index(collect(return RANGE, criteria RANGE 1, criteria 1, criteria RANGE 2, criteria 2),1)

    · Share on FacebookShare on Twitter

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    Answer ✓

    Hey @J.H. Lim

    VLOOKUP will always return the first result found which is why unique values are used so only one answer can be found for each search. Cases like this one can be solved by introducing unique values and assigning them to the rows. Most people just start with 1 and work there way up from there.

    If that isn't possible because you don't have anyway to edit the main sheet you can try creating unique values through searching for multiple criteria with INDEX/COLLECT

    =index(collect(return RANGE, criteria RANGE 1, criteria 1, criteria RANGE 2, criteria 2),1)

    · Share on FacebookShare on Twitter
  • J.H. Lim
    J.H. Lim ✭

    Thank you! The INDEX/Collect solution seems to work for what I needed (at least on the example sheet).

    Going to try implementing it on my actual sheet now. Fingers crossed!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!