Lookup table using 2 entries to lookup a value....
Posted by Chris Rock on November 15, 2001 11:26 AM
I have a 3 column table with the following data: TIME, PRODUCT, and CALLS.
What I'd like to do, in cell G2, is get the number of CALLS for a particular PRODUCT (typed in F2) at a particular TIME (typed in E2).
Does this require an array formula? Can someone share with me how this works?
A VLookup won't do the trick, because it's only looking up on ONE criteria. This needs to lookup on 2 criteria.
Thanks.
Posted by Aladin Akyurek on November 15, 2001 11:49 AM
Chris --
You didn't provide the exact location of the table, so I'll assume some names:
The range where you have times is named TIMES (excluding the label). The range that houses products is named PRODUCTS (excluding the label).
The range that houses calls is named CALLS (excluding the label).
If you so desire, you can give the above names to the respective ranges via the Name Box on the Formula Bar.
The formula that you can use to retrieve the number of calls is:
=IF(SUMPRODUCT(ISNUMBER(MATCH(E2&"-"&F2,TIMES&$PRODUCTS,0))+0),INDEX(CALLS,SUMPRODUCT(MATCH(E2&"-"&F2,TIMES&PRODUCTS,0))),"")
Aladin
======
Posted by Chris Rock on November 15, 2001 11:51 AM
I got it!!
{=INDEX(C:C,MATCH(E2&F2, A1:A50&B1:B50,0))}
It's an array formula.
I found a post here that helped me:
http://mrexecel.com.wwwboard/messages/5773.html
