Applying values to a database based on multiple criteria sets

[Baldred]

Im sure this may be a bit confusing, but i have a database similar to the first image.

What i want to do is, based on the data on "Info" ranges, apply a value to the "Value" ranges. For that i had formulas such as "=if(A1 = "A",1,0)" or "=if(and(B1 = "red",C1 = "pear"),1,0)" which would get me the second image.

My problem is, i need to have these criterias as something easly accessible and modifiable for the user, for that i made another sheet that has the criterias, like the third image.

I first thought of using Xlookup to build a formula that would check if its line fit any of the criteria listed on the other table and then pull the value from the respective "Value" range, but i couldnt think of a way to have it work with the ones with comlex criteria, like the "red and pear"

Another problem i can have is that, intead of getting just fruits in "Info3", i might get say "pear juice", and i would need that to be consiered as "pear" too.

Is there any clear solution for that? Thank you!

 

[Baldred]

I know that you can use a xlookup with multiple criteria using something like this:

=XLOOKUP($A2&$B2&$C2,Criteria!$A$2:$A$3&Criteria!$B$2:$B$3&Criteria!$C$2:$C$3,Criteria!E$2:E$3,0)

The problem with this is that it wont work with the lines that only need to match with a single criteria, like the "A1 = A" that i mentioned.

For the "pear juice" part, something like this could work, but i dont know how to have all of those working together.

=XLOOKUP(1,--ISNUMBER(SEARCH("pear",Criteria!$C$2:$C$3)),Criteria!E$2:E$3,2)