what is the suitable function
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
Best Answer

Try an INDEX/COLLECT like so:
=INDEX(COLLECT({Area Column}, {Plot Column}, @cell = Plot@row, {Valve Number Column}, @cell = [Valve Number]@row), 1)
Answers

Try an INDEX/COLLECT like so:
=INDEX(COLLECT({Area Column}, {Plot Column}, @cell = Plot@row, {Valve Number Column}, @cell = [Valve Number]@row), 1)

it works well
thank you very much

Happy to help. 👍️

please could you explain each part of this syntex

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.

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

The previously suggested formula should be pulling in exactly what you are wanting.
Help Article Resources
Categories
Check out the Formula Handbook template!