Can I lookup a value based on two criteria?

Richard King

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.

Genevieve P.

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.

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.

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,

Genevieve P.

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

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.

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

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.

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

Hi Genevieve

Now working, many thanks for your help.

Richard