Match Multiple Criteria one exact match one nearest match

[gbrew584]

I am stumpped as to how to accomplish this task, and I am hoping someone can point me in the right direction.

I am using excel 2010. I have a worksheet called Rates, that has thre columns, "Facility", "Date" and "Rate". The column for "Facility hold the facility name, the "Date" Column holds the date the rate canged and the "Rate holds the dollar rate.

Facililty Date Rate
South Side1 07/01/13 24.00
West Side1 07/01/13 35.00
South Side1 09/01/13 29.00
North Side2 10/01/13 10.00
West Side1 10/01/13 38.00

I have another worksheet that has a customer name, the facility, and the month the purchase is made.
(all customer purcases will be at the end of the month). What I am looking for is a way to match the rate with the facility and the date. The Rate would be in effect until the new rate is entered. The rate for the southside is in effect from 7/01/13 until 8/31/13.

Customer Date Facility Rate
Bob 07/31/13 Westside1 35.00 (I don't know what formula to use here)
Jerry 10/31/13 westside1 38.00
Sue 08/31/13 Southside1 24.00
Sue 09/30/13 Southside1 29.00

The facility would need to be an exact macth but the date would need to be closest(??) match. For sue I need to find the rate that is good for 8/31/13 (24.00) and the new rate for her 09/30/13 purchase.

I tired using an array Match(B1&C1,'Rate!'B1:B6&A1"A6) but that gives me bad results when the facility names start with the same letters. I'm not sure what type of fomuala I need. I've used excel a little bit but this has me stumpped. I would appriciate if someone could give me a hint as to what I should do.

Thanks, in advance!!

 

[AhoyNC]

See if one of the two formulas below will work for you.
You have the Facility name spelled differently in your 2 tables above.

If the names are the same in your workbooks then use the formula in cell D1.
If they are like you have them in your post try the formula in cell D8

Both are array formulas and must be enter with CTRL-SHIFT-ENTER

Excel Workbook
	A	B	C	D
1	Bob	7/31/2013	West Side1	35
2	Jerry	10/31/2013	West Side1	38
3	Sue	8/31/2013	South Side1	24
4	Sue	9/30/2013	South Side1	29
5
6
7
8	Bob	7/31/2013	Westside1	35
9	Jerry	10/31/2013	Westside1	38
10	Sue	8/31/2013	Southside1	24
11	Sue	9/30/2013	Southside1	29
Sheet2

Excel Workbook
	A	B	C
1	South Side1	7/1/2013	24
2	West Side1	7/1/2013	35
3	South Side1	9/1/2013	29
4	North Side2	10/1/2013	10
5	West Side1	10/1/2013	38
Rates

 

[gbrew584]

Thank you for the reply. I was not able to get to my computer yesterday. I will give your formulas a try, today and I'll let you know how it goes. Thank you very much for your help

 

[gbrew584]

Wow that's cool. First off, sorry for the spelling errors. The locations should be spelled the same in both worksheets. Sorry you had to go to the trouble of creating two formulas becuase of my sloppy typing.

Second, I tried your formula in D1 and I think it is working!! That is so smart, I never would have thought to use a lookup inside of the match and then use the results as an exact match, that is so cool. I am dumbfounded.

Thanks very very much. And thanks for teaching me a new trick.

 

[AhoyNC]

You're welcome. Thanks for the feedback. It works with the small sample you had, but do check on your actual data. Your rates data had the dates in an ascending order, may be a problem if dates are random.