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

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:D10>F1),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