Forum Discussion
vlookup + match
Hello. I need help with a formula combining vlookup and match. Somehow, the return is not correct. My formula right now is: VLOOKUP($B9,$B$2:$B$5,MATCH($A9,$A$2:$A$5,0),FALSE)
But it's wrong apparently. Why?
=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.
7 Replies
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."
=IFERROR(INDEX($C$2:$C$5,MATCH(1,($A$2:$A$5=A9)*($B$2:$B$5=B9),0)),"incorrect")
This is a job for INDEX and MATCH in my understanding. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2021.
This seems to work on the first example I provided. But how can I make the same criteria work when the data is built differently? I will attach an update.
=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.
