Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Banned
 
Posts: 2
Default

Vote cho bạn
_________________
can ho moonlight park view
  #4   Report Post  
Banned
 
Posts: 3
Default

Cảm ơn bạn!
  #5   Report Post  
Junior Member
 
Posts: 2
Default

Cảm ơn bạn!


  #6   Report Post  
Junior Member
 
Posts: 2
Default

Cảm ơn bạn!
  #7   Report Post  
Banned
 
Posts: 1
Default

Bạn đã có dự định gì cho chủ nh*t?
_________________
mua hộ h*ng mỹ
https://t.co/pYTA2l28fY
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Formula Needed sweetmans Excel Discussion (Misc queries) 2 January 22nd 10 08:16 PM
Lookup Formula Needed Deepak Excel Discussion (Misc queries) 3 December 29th 08 09:05 PM
Complicated lookup/match formula help needed! Jason[_11_] Excel Worksheet Functions 2 March 21st 08 12:39 AM
Complicated formula help needed please Alex H[_2_] Excel Worksheet Functions 2 September 6th 07 03:17 PM


All times are GMT +1. The time now is 11:34 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"