Multi-Criteria Lookups

[hatman]

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.xls
	F	G	H	I	J	K
7	tom	chevy	10		polly	chevy
8	****	ford	20		130
9	harry	pontiac	30
10	jane	subaru	40
11	polly	amc	50
12	kelley	volvo	60
13	scott	chevy	70
14	brian	ford	80
15	tom	pontiac	90
16	****	subaru	100
17	harry	amc	110
18	jane	volvo	120
19	polly	chevy	130
20	kelley	aztek	140
21	paul	pontiac	150
22	brian	subaru	160
23	tom	amc	170
24	****	volvo	180
25	harry	chevy	190
Sheet1

 

[NateO]

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]

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]

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]

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.