formula needed for complicated lookup
I have a rather complicated workbook that I have an issue with
Column A Column B Column C Column H
Date Amount ($) Transaction Type (x,p,or f)
12/17 100.00 Payment x
12/19 (45.00) Purchase f
12/22 (55.00) Interest f
12/27 (38.55) Purchase p
What I am trying to do is return the Column H value based on the closest match (not necessarily exact) in column A AND an exact match in Column C of "Payment".. If Column A has an exact match but is not also "Payment" in Column C, it should find the next closest (previous) result that definitely has "Payment" in Column C.
So.. if I wanted to find the closest payment to 12/27 and return the Column H value,
I would expect the correct result to be 'x'.
The main issue is that even though I'm looking up 12/27 and there is an exact match in column A, the 12/27 row does not also show "Payment" in Column C.
Table data is always sorted by date
I do not want to change the table