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
-
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
-
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.
-
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
-
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
-
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
-
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.
-
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
-
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,
-
Hi Genevieve
Now working, many thanks for your help.
Richard
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!