Multiple Lookup with Criteria
 

Hi,
I am trying to do a lookup and having problems! Multiple Companies with
multiple products with different prepay dates. Wanting to use today's date
as the main criteria and return the correct % based if the order was placed
prior to the date below here is the example:

DATE IS 10/15 ON ORDER WANT IT TO LOOKUP PROD A/MFG A / CASH AND CHECK WHICH
DATE IS THEN 10/15 AND RETURN THE CORRECT % FOR A DISCOUNT

PROD A MFG A CASH 10/10/09 10%
PROD A MFG A CASH 11/10/09 8%
PROD B MFG A CASH 10/10/09 10%
PROD B MFG B CASH 11/15/09 7%
PROD B MFG A CR CARD 11/15/09 6%

HELP

Multiple Lookup with Criteria
 

Lookup criteria:

G2 = PROD A
H2 = MFG A
I2 = CASH
J2 = 10/15/2009

Maybe this array formula** :

=INDEX(E2:E6,MATCH(1,(A2:A6=G2)*(B2:B6=H2)*(C2:C6= I2)*(D2:D6J2),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"RYGUY30" wrote in message
...
Hi,
I am trying to do a lookup and having problems! Multiple Companies with
multiple products with different prepay dates. Wanting to use today's
date
as the main criteria and return the correct % based if the order was
placed
prior to the date below here is the example:

DATE IS 10/15 ON ORDER WANT IT TO LOOKUP PROD A/MFG A / CASH AND CHECK
WHICH
DATE IS THEN 10/15 AND RETURN THE CORRECT % FOR A DISCOUNT

PROD A MFG A CASH 10/10/09 10%
PROD A MFG A CASH 11/10/09 8%
PROD B MFG A CASH 10/10/09 10%
PROD B MFG B CASH 11/15/09 7%
PROD B MFG A CR CARD 11/15/09 6%

HELP

Multiple Lookup with Criteria
 

With date 10/15 in cell F1 and your data in colA:E try the below array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=INDEX(E1:E10,SMALL(IF((A1:A10="Prod A")*(B1:B10="MFG a")*
(C1:C10="Cash")*(D1:D10F1),ROW(A1:A10)),1))

If this post helps click Yes
---------------
Jacob Skaria


"RYGUY30" wrote:

Hi,
I am trying to do a lookup and having problems! Multiple Companies with
multiple products with different prepay dates. Wanting to use today's date
as the main criteria and return the correct % based if the order was placed
prior to the date below here is the example:

DATE IS 10/15 ON ORDER WANT IT TO LOOKUP PROD A/MFG A / CASH AND CHECK WHICH
DATE IS THEN 10/15 AND RETURN THE CORRECT % FOR A DISCOUNT

PROD A MFG A CASH 10/10/09 10%
PROD A MFG A CASH 11/10/09 8%
PROD B MFG A CASH 10/10/09 10%
PROD B MFG B CASH 11/15/09 7%
PROD B MFG A CR CARD 11/15/09 6%

HELP