# Multi-Criteria Lookups



## hatman (May 28, 2008)

Hmm, one thing that I haven't seen around is Multi-Criteria Lookups... I was driven to demonstrate how to do one in a contest of one-upmanship a few weeks back, and it's sorta been rolling around in my head since then.  In that particular thread, my formula was flawed, since Lookup() needs the search vector to be ascending alphabetical... but for the gist of that thread, it didn't much matter anyway.  I'm surprised that Chip doesn't cover the topic in his Lookup Article.  Was wondering if other people have seen this around, and I just missed it.Csacading_List.xlsFGHIJK7tomchevy10pollychevy8****ford201309harrypontiac3010janesubaru4011pollyamc5012kelleyvolvo6013scottchevy7014brianford8015tompontiac9016****subaru10017harryamc11018janevolvo12019pollychevy13020kelleyaztek14021paulpontiac15022briansubaru16023tomamc17024****volvo18025harrychevy190Sheet1


----------



## NateO (May 28, 2008)

It's not transparent to me that what you're solving is any more simple than this:

_=SUMPRODUCT(-(A1:A19=E1),-(B1:B19=F1),(C1:C19))_

Not to mention that this takes into account for multiple returns.

Or for looking up:

_{=INDEX(C1:C19,MATCH(1,(A1:A19=E1)*(B1:B19=F1),0))}_

I changed your ranges to line up with A1 and so forth. 

Concatenation is a pretty expensive operation and you're doing quite a bit of it. Without testing the performance, I would guess it drags. Do you know how well this performs?


----------



## barry houdini (May 28, 2008)

Of course SUMPRODUCT's only good for numeric results. You can use LOOKUP, without CSE and without sorting, e.g.

_=LOOKUP(2,1/((A1:A19=E1)*(B1:B19=F1)),C1:C19)_

although this works slightly differently to Nate's suggested INDEX/MATCH because it'll find the last match not the first.


----------



## hatman (May 29, 2008)

As Barry pointed out, the Sumproduct() option doesn't work for non-numeric data.  In contriving the data, I overlooked the Sumproduct() option that my numeric column allowed, even though I had a Lookup style solution in mind from the start.

I hadn't bothered to test performance of the concatenation approach... I wouldn't be surprised to find that it's quite expensive.  

Between the Index() versus Lookup() solutions to this, the former is slightly more intuitive to me, though both solutions are building a lookup array in a similar fashion.  I had to examine the Lookup() solution more closely before I realized why the Div by Zero results are preferable to 1's.  If only a single match is expected, I'm not sure that either method has a benefit ove rthe other... though I could envision comparing the results of the two to determine if more than one match exists.

Thank you both for showing me a better way.  Outside of Sumproduct(), building an array by coercing numeric results from boolean comparisons is still not as intuitive to me as it is to you guys, but every time I get exposed to it in other applications, it makes a deeper impression.  Maybe there's hope for me yet!


----------



## NateO (May 29, 2008)

Right, I didn't mean to imply that Sumrproduct() is good for pure-play Lookups in general, I just noted it would work with your specific data set. Which is why I provided the 2nd construct, as I knew that's what you really after.

You are welcome.


----------

