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.
If you still want a VLOOKUP to work in your situation, try this in cell C9, and copy it down:
=VLOOKUP($A9,$A$2:$C$5,3,0)
The VLOOKUP should be looking for the calorie count in the third column of the range $A$2:$C$9, and that formula I give you above is that way that will work. Your use of MATCH was giving your VLOOKUP a different number each time, and a number that had nothing to do (except accidentally) with the third column.
Here is a good reference for learning the several ways to look things up in data arranged in tabular form. Good functions to start with are VLOOKUP, HLOOKUP, as well as INDEX used with MATCH. Each has its advantages in certain circumstances. And as you see from the all-inclusive list, there are many ways to nuance references to tabular data.
- Thank you - but 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.
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."
