Forum Discussion
vlookup + match
=IFERROR(INDEX($I$9:$L$12,MATCH($H2,$H$9:$H$12,0),MATCH($G2,$I$8:$L$8,0)),"incorrect")
This returns the results for fruit and country however in the database there are no values for months. Therefore we can't include criteria for months in the formula.
it does not quite give me what I need. For example, where Melon/Red is, should be 0 and not 32 because Melon/Red is not matching Melon/Orange criteria.
Ah, it wasn't clear to me that you were wanting to match BOTH criteria. Although I need to ask you what this is about.....I'm assuming you're giving this as a test case, that what you're really working with is NOT going to be looking up calories for fruits of various colors, since zero would not be a correct answer for red melon in reality.
Having looked at the example you just gave OliverScheurich , I'm not clear on what you're trying to do. Despite that lack of clarity, I would suggest you also look into the FILTER function, one of the relatively recent Dynamic Array functions....it works well with multiple criteria. If there's only a single answer to a FILTER with multiple criteria it will produce that; if nothing meets the criteria, it can say something to the effect "no match found."
