VBA - Lookup with multiple conditions/criteria

Hi,

I attached a wb2 with a table named "Table1".

Put this code in wb1:

Please Login or Register  to view this content.

Regards,
Elio Fernandes

Efernandes,

Thank you so much for trying to help me out.

If I can avoid macro...I'll definitely avoid it completely!!! However, I have to do this in macro, reason being, my manager wants it to. As well, I tried to have condensed a formula, but excel won't take it, probably it's too long.

Anyway, I've attached a sample of it...wb1 and wb2.

wb1 is how part of the actual data is...and it contains all the "criteria" that I need to look at. Column G (currency) and column H (rate) is how it would have been with the formula.

How I get the currency:
1st. Look at column F (in this case, there's only 1 partner code id) - partner code id is AUSOP
2nd. Look at column B and determine the "earliest" and "latest" date. In our data, 201012 is earliest date and 201208 is the latest date in column B.
3rd. With the above 2 criterias, it's time that we look at wb2.
4th. In wb2, 'Partner Rates' sheet, noticed that we have 2 AUSOP partner code in column A (colored in yellow). But the effective date and end date of these 2 are different. With the vlookup array formula, it has taken row 4 (colored green) since our earliest date in wb1 (201012) is <= effective date in wb2 of row 4, the same with our latest date in wb1 (201208) >= end date in wb2.
5th. Since there's a match, we take column F.

How I get the Rate:
1st. The step is the same as above where the code has to determine which row to take after meeting the criteria (partner code, earliest date <= effective date, and latest date>= end date)
2nd. on wb1 at column C, we determine if it's a "GPRS, SMS or Voice".
3rd. If it's GPRS, we return the rate (column O of wb2) in the determined row on wb2 (in our example, row 4).
4th. If it's SMS, we return the rate (column N of wb2) in the determined row on wb2 (in our example, row 4).

5th. Voice would be a little troublesome, since it has to look at the country (column E) in wb1, then look up at wb2 "Special Destination" Sheet to determine if it's 'YES' and then return the rate value (column J of wb2 "Partner Rates" sheet). At this point, I'll ignore this for a while and try to do it myself.

PS: wb2 has named ranged.

Below is part of the code where I try to do a multiple search criteria...but just don't know how to.

Please Login or Register  to view this content.