I'm trying to build a function which checks for multiple criteria in an array and if met, returns a value from a certain column in the array.
INDEX combined with MATCH is generally great for this but so far I have used it only when certain criteria was checked only in certain column and row and all the tutorials I've found also use it in this way.
E.g. when I check for values A2 in column C and value B2 in column D at the same time and want to return value from 6th column when these two criteria are met, I use this:
However, now I need the to check for multiple criteria to be met in a single row but not knowing in which columns to expect which criteria.
So I would need to use something like
but obviously it doesn't work this way.
Can you please suggest the proper syntax or function to achieve this?