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 crosssheet 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 setup.

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 crosssheet 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 setup.

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({FMHSEHAVS02 Range 6}, {FMHSEHAVS02 Range 2}, $[Tool ID]@row, {FMHSEHAVS02 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
Check out the Formula Handbook template!