Can I lookup a value based on two criteria?

Hi ,

I have two sheets, sheet 1 holds fixed data and sheet 2 is populated via a form.

I want a cell in sheet 2 to return data from a cell in sheet one when two column values in sheet two are matched to two column values in sheet 1.

Is this possible?

Hope this makes sense.

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi Richard,

    You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell's value, assuming that only one value meets all the criteria).

    The way JOIN(COLLECT works is that you first list the range that has the value you want returned from your fixed data sheet, and then list each range and criteria in the current, second sheet afterwards.

    For example:

    =JOIN(COLLECT({Sheet 1 value to return}, {Sheet 1 Range 1}, [Column 1]@row, {Sheet 1 Range 2}, [Column 2]@row))


    The ranges in {these} are cross-sheet references to the column with the fixed value in your first sheet. The values in [these] represent the column in sheet 2, where the formula is being written.

    You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References

    Let me know if you have any questions as you build this out! (Screen captures of both sheets without any sensitive data would be helpful).

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Richard King

    Can you copy/paste in the formulas you are using? (The Join(Collect as well as the multiplication one.)

    You can use a ROUND function to Round numbers, or a VALUE function to turn numbers that are being pulled through as text string back into numbers... but it will depend on your current formulas and set-up.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Richard King

    The values are appearing in the left of the cell, which indicates that they're coming through as a text string (see the question "Why are my numeric values not calculating correctly?" at the bottom of the Frequently Asked Questions About Using Formulas article).

    Try using the VALUE() function wrapped around each referenced cell in your multiplication formula:

    =VALUE([Cell 1]@row) * VALUE([Cell 2]@row))

    Let me know if this works for you!

    Cheers,

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi Richard,

    You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell's value, assuming that only one value meets all the criteria).

    The way JOIN(COLLECT works is that you first list the range that has the value you want returned from your fixed data sheet, and then list each range and criteria in the current, second sheet afterwards.

    For example:

    =JOIN(COLLECT({Sheet 1 value to return}, {Sheet 1 Range 1}, [Column 1]@row, {Sheet 1 Range 2}, [Column 2]@row))


    The ranges in {these} are cross-sheet references to the column with the fixed value in your first sheet. The values in [these] represent the column in sheet 2, where the formula is being written.

    You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / @row function / Cross Sheet References

    Let me know if you have any questions as you build this out! (Screen captures of both sheets without any sensitive data would be helpful).

    Cheers,

    Genevieve

  • Richard King
    Richard King ✭✭✭✭

    Hi Genevieve,

    When using JOIN(COLLECT, can I use the cells in a formula. I want to do a simple multiplication of two cells but get the error message #INVALID OPERATION.

    Also numbers are copied over with too many decimal places.

    Below are screenshots.

    JOIN(COLLECT Sheet

    Fixed Data Sheet

    Many Thanks

    Richard

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Richard King

    Can you copy/paste in the formulas you are using? (The Join(Collect as well as the multiplication one.)

    You can use a ROUND function to Round numbers, or a VALUE function to turn numbers that are being pulled through as text string back into numbers... but it will depend on your current formulas and set-up.

  • Richard King
    Richard King ✭✭✭✭

    Hi Genevieve,

    This formula is in the 'Trigger Time in Minutes to reach 90 Points' column and looks for the Tool ID and Tool ID & Action values.

    =JOIN(COLLECT({FM-HSE-HAVS-02 Range 6}, {FM-HSE-HAVS-02 Range 2}, $[Tool ID]@row, {FM-HSE-HAVS-02 Range 4}, $[Tool ID & Action]@row))

    Thanks

    Richard

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Richard King

    The values are appearing in the left of the cell, which indicates that they're coming through as a text string (see the question "Why are my numeric values not calculating correctly?" at the bottom of the Frequently Asked Questions About Using Formulas article).

    Try using the VALUE() function wrapped around each referenced cell in your multiplication formula:

    =VALUE([Cell 1]@row) * VALUE([Cell 2]@row))

    Let me know if this works for you!

    Cheers,

  • Richard King
    Richard King ✭✭✭✭

    Hi Genevieve

    Now working, many thanks for your help.

    Richard

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!