Match two columns (one approximate) in excel and return a third

[pernorman]

Hello Excel experts!

I consider myself an expert user of Excel but I really got stuck on this problem . I prefer a solution with built-in formulas but if that isn't possible a VBA solution is fine.

I have one table (the Rating table) where I occasionally put in data that is event driven: e.g. a change in a banks credit rating. That happens once in a while and I am recording the bank, actual date that the credit rating was changed, and the credit rating. In the second table (the Lookup table) I have daily cash balance with each bank and would like to tag each balance with the relevant rating of that bank. This means that I would like to search for the row in the Rating table that matches the bank and the date that is closest i.e. equal to or less than the cash balance date. Please see the tables below.

Rating Table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Bank[/TD]
[TD]Date[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[TD]Citibank[/TD]
[TD]01/05/2014[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]RBS[/TD]
[TD]01/05/2014[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Citibank[/TD]
[TD]05/03/2014[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]Citibank[/TD]
[TD]08/30/2014[/TD]
[TD]A-[/TD]
[/TR]
[TR]
[TD]RBS[/TD]
[TD]08/30/2014[/TD]
[TD]A-[/TD]
[/TR]
</tbody>[/TABLE]

Lookup table


[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Cash balance[/TD]
[TD]Bank[/TD]
[TD]Rating[/TD]
[TD]Answer:[/TD]
[/TR]
[TR]
[TD]03/31/2014[/TD]
[TD]1000[/TD]
[TD]Citibank[/TD]
[TD]Formula here...[/TD]
[TD]A+[/TD]
[/TR]
[TR]
[TD]03/31/2014[/TD]
[TD]2000[/TD]
[TD]RBS[/TD]
[TD]Formula here...[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]06/30/2014[/TD]
[TD]3000[/TD]
[TD]Citibank[/TD]
[TD]Formula here...[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]06/30/2014[/TD]
[TD]4000[/TD]
[TD]RBS[/TD]
[TD]Formula here...[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]09/30/2014[/TD]
[TD]5000[/TD]
[TD]Citibank[/TD]
[TD]Formula here...[/TD]
[TD]A-[/TD]
[/TR]
[TR]
[TD]09/30/2014[/TD]
[TD]6000[/TD]
[TD]RBS[/TD]
[TD]Formula here...[/TD]
[TD]A-[/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

Welcome to the board!

With your rating table copied to A1:C6 and lookup table to A9:E15 (including headers), the following formula works for me.

=INDEX($C$2:$C$6,MATCH(2,1/(($A$2:$A$6=C10)*($B$2:$B$6<=A10))))

Note that this must be array confirmed using Shift, Ctrl & Enter, otherwise it will return a #VALUE! error.

edit:-

forgot to mention that the lookup table needs to have the dates ascending, otherwise it will take the last date in the list that is on or before the reference date, even if that is not the most recent.

 

[pernorman]

Wow! How that worked, I have no idea.. Much appreciated though!!

Can you please explain the logic behind the formula!??

 

[jasonb75]

Sure, or I'll try to anyway

($A$2:$A$6=C10) works in the same way as a formula using =IF(x=y,true,false), in that matching rows return TRUE, likewise for the subsequent part, ($B$2:$B$6<=A10).

Remembering the way that logical functions work, TRUE = 1, FALSE = 0, so the results are multiplied, TRUE*TRUE = 1, anything else = 0, so you end up with an array, {0,1,0,1,1,0} or something similar.

Then the array is used as a divisor, 1/1 = 1, 1/0 = #DIV/0! error. The MATCH function will ignore the errors caused by the non matching rows and find the last number (using approximate match, exact match would find the first record, not last!) , which will be the last row with an exact match for the bank name (=C10) and an approximate match for the date(<=A10).

Hope that makes sense, the formula is easier to write than to explain.

 

[pernorman]

THANKS! That makes sense. I've just discovered a totally unexplored part of Excel!