formula needed for complicated lookup
 

Hello,

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

formula needed for complicated lookup
 

Hi,

Am Sun, 20 Nov 2016 02:52:36 +0000 schrieb coreyk88:

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.

the expected date in K1, the transaction type in L1.
Then try for a date +/- 3 days:
=INDEX(H:H,SUMPRODUCT(--(A2:A10=K1-3),--(A2:A10<=K1+3),--(C2:C10=L1),ROW(2:10)))



Regards
Claus B.
--
Windows10
Office 2016

Vote cho bạn
_________________
can ho moonlight park view

Cảm ơn bạn!

Cảm ơn bạn!

Cảm ơn bạn!

Bạn đã có dá»± định gì cho chủ nháº*t?
_________________
mua há»™ hÃ*ng mỹ
https://t.co/pYTA2l28fY