Cross-reference formula comparing 2 cells to return a 3rd

I've been trying all the suggestions from the formula handbook and the community but I'm still doing something wrong. Basically, I have 2 sheets. The 1st sheet needs to have a column that returns a value from the 2nd sheet. But it should only return that value if the email address and item number match between the two sheets.

My latest tries are below...any advice?

=INDEX(COLLECT({Second Sheet Range 4}, {Second Sheet Range 2}, [Email Address]@row, {Second Sheet Range 3}, [Item Number]@row), 1)

=JOIN(COLLECT({Second Sheet Range 4}, {Second Sheet Range 2}, [Email Address]@row, {Second Sheet Range 3}, [Item Number]@row))

Best Answer

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓

    @ccarriger

    Since your ranges have { } around the column names, the amount of rows wouldn't matter.

    Have you tried removing one of the criteria and getting a return value? Even though the COLLECT function is commonly used for multiple criteria, you can still use a single criteria if you wanted.

    Check each criteria individually to make sure you're getting a value.

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭

    Hello @ccarriger

    The first INDEX/COLLECT function looks like it would work.

    Are there any discrepancies in the column properties? Like the email address is a text but the lookup range are contacts?

  • Thanks, @MichaelTCA! I checked and they are all text/number fields so that's not a factor thankfully. Would it make a difference that my second sheet is significantly shorter than the first sheet? I assume that shouldn't matter, but sheet 1 has about 10k rows and sheet 2 has less than 4k.

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓

    @ccarriger

    Since your ranges have { } around the column names, the amount of rows wouldn't matter.

    Have you tried removing one of the criteria and getting a return value? Even though the COLLECT function is commonly used for multiple criteria, you can still use a single criteria if you wanted.

    Check each criteria individually to make sure you're getting a value.

  • @MichaelTCA, it ended up working on a different sheet so no idea if I had something wrong on the original sheet, but it's working now! Thanks for your help :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!