Multiple Lookup with Criteria

[RYGUY30]

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

 

[Jacob Skaria]

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")*(D110>F1),ROW(A1:A10)),1))

If this post helps click Yes