what is the suitable function

Ibrahim Khalaf

I have 2 sheets, the first one is a table that shows the following

and I have another sheet all what i need when i write the values in the first and the second column the data in the third columns appear

what suitable function  could be used

Paul Newcome

Try an INDEX/COLLECT like so:

=INDEX(COLLECT({Area Column}, {Plot Column}, @cell = Plot@row, {Valve Number Column}, @cell = [Valve Number]@row), 1)

Paul Newcome

Try an INDEX/COLLECT like so:

=INDEX(COLLECT({Area Column}, {Plot Column}, @cell = Plot@row, {Valve Number Column}, @cell = [Valve Number]@row), 1)

Ibrahim Khalaf

it works well

thank you very much

Paul Newcome

Happy to help. 👍️

Ibrahim Khalaf

please could you explain each part of this syntex

Paul Newcome

You COLLECT all of the rows from the first range where the range/criteria sets match then you INDEX the first one from the array.

Ibrahim Khalaf

thank you, but I'm still struggling in understanding functions and formulas what is your advice regarding that

how to apply what is in my mind using functions and formulas

Paul Newcome

The previously suggested formula should be pulling in exactly what you are wanting.