How do I Create a Multi-Column VLOOKUP using Criteria in Two Different Cells?
With great difficulty… unless you know how to write an array formula 🙂
Yes, just when you thought you knew Excel – an array formula comes along and presents a whole new world of possibilities.
But what is an array formula?
Array formulas enable us to index ranges that interact with each other in order to calculate a result using corresponding ranges and multiple criteria – even when the function we’re using doesn’t allow multiple ranges or criteria.
Wow, that sounds pretty complicated. I guess the truth is that array formulas are complicated. Once you’ve gotten a handle on them though, a whole new world of Excel possibilities opens up.
Go on, give it a go… if you think you’re up to it.
This example uses IF, ISERROR and MATCH functions encased in an array to keep it relatively simple, but you could easily combine it with INDEX to return corresponding values instead of the MATCH/NO MATCH shown in the example.