Look for matches in two columns and return list of contents of a third column

thelittleredfox

New Member
Joined
Dec 15, 2014
Messages
17
Hi,

I have the following spreadsheet, which lists all payments made under client accounts by date. (Please excuse the horrible layout).

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Row number
[/TD]
[TD]Client Name
[/TD]
[TD]Payment Date
[/TD]
[TD]Payment Amount
[/TD]
[TD]True or False
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]2[/TD]
[TD]Dominic Holden[/TD]
[TD] 20/04/2018[/TD]
[TD]£6.58[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]3[/TD]
[TD]Gary Baines[/TD]
[TD]02/06/2018[/TD]
[TD]£11.22[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]5[/TD]
[TD]Teri Kane[/TD]
[TD]05/09/2018[/TD]
[TD] £16.73[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]6[/TD]
[TD]Julian Baker[/TD]
[TD]23/09/2018[/TD]
[TD]£9.31[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]7[/TD]
[TD]Gillian Houghton[/TD]
[TD]26/09/2018[/TD]
[TD]£8.10[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]8[/TD]
[TD]Thomas Hunt[/TD]
[TD]14/10/2018[/TD]
[TD]£7.99[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]9[/TD]
[TD]James Clarke[/TD]
[TD]28/10/2018[/TD]
[TD]£2.56[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]


The sheet is called "data sheet" and currently has 5000 lines of data, which will probably increase to around 10,000 by the end of the year.

What I would like to do is to create a new tab which lists all records which fall within a specified time period AND where the value in column E is TRUE. There might be multiple results for the same date where column E is TRUE. I'd like it to look something like this:

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client Name
[/TD]
[TD]Payment Date
[/TD]
[TD]Payment Amount
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Julian Baker[/TD]
[TD]23/09/2018[/TD]
[TD]£9.31[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gillian Houghton[/TD]
[TD]26/09/2018[/TD]
[TD]£8.10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]James Clarke[/TD]
[TD]28/10/2018[/TD]
[TD]£2.56[/TD]
[/TR]
</tbody>[/TABLE]


I'm guessing I need to use an array formula but I've tried and just can't manage to create one that works. Any help would be appreciated!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
I guess, you are looking for a dynamic range of time period. So you need to be able to provide the range limits "on the go".

Are you looking for a formula or a VBA code?
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top