List of accounts if match date

richmcgill

Board Regular
Joined
Feb 4, 2019
Messages
86
Office Version
  1. 2016
Platform
  1. Windows
I have two tabs. The first tab has a list of numbers numbers with dates. The second tab has the same loan numbers but has amounts with the account number.

I know a VLOOKUP will work here but my boss is wanting a formula. I know a pivot table will work.

So if I had a cell with a date and can change at will for this instance 06/01/2019 make the the column populate with the account numbers and the amounts with the account number. I only want the accounts (not a count) that match the date and populate the amounts on the other tab.

What would this formula look like?

[TABLE="width: 144"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]First Tab
Loan Number [/TD]
[TD]Paid Date[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD]8/1/2019[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD]7/1/2019[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD]6/1/2019[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD]6/1/2019[/TD]
[/TR]
</tbody>[/TABLE]


Second Tab
[TABLE="width: 195"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Loan No[/TD]
[TD]Compare Interest[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD="align: right"]-694.17[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD="align: right"]-691.04[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD="align: right"]-630.2[/TD]
[/TR]
[TR]
[TD]1233[/TD]
[TD="align: right"]-617.61[/TD]
[/TR]
[TR]
[TD]2129[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3025[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3921[/TD]
[TD="align: right"]-601.83[/TD]
[/TR]
[TR]
[TD]4817[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]5713[/TD]
[TD="align: right"]-572.33[/TD]
[/TR]
[TR]
[TD]6609[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]7505[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8401[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]9297[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10193[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]11089[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Maybe a better way yo ask

I need help with a formula


Tab 1
Cell a1 with a manually entered date

Tab 2
Column A Column B
Loan # Dollar Amount

Tab 3
Column A Column B
Loan # Date

If the date in tab 3 matches the date on tab 1
and Loan Number both tab 2 & 3 match

Show me the loan # and the Dollar Amount on tab 4

I need a formula other than a vlookup or pivot table.

I was trying to use index with match but I could not get it to work.

Thank you!
 
Upvote 0
Are the loan numbers pre-populated on tab 4?

I know a VLOOKUP will work here but my boss is wanting a formula. I know a pivot table will work.
VLOOKUP is a formula. Why can't you use VLOOKUP? Is this some sort of homework assignment where they want you to use other methods?
 
Upvote 0
There are already five or more layers of Vlookups she does not want anymore. She wants to change the date and get the results listed based on the information presented.
 
Upvote 0
You might want to consider trying to use filters and/or VBA.

However, it appears that you really may be using the wrong tool for the job. This is more of a relational database type of thing that would probably be handled much more efficiently in a database program like Access.
 
Upvote 0
I see index match formulas working in other very similar scenarios. I guess I will try on my own. Thanks.
 
Upvote 0
You never answered my initial question back in post 4:
Are the loan numbers pre-populated on tab 4?
Are the loan numbers on all the tabs all in the same order?
So, if you look at line 99 on each of the tabs, would they all be referencing the same loan number?

=IF(AND(Q2=$AB$1,B2=N2),V2,"")

This is one solution.
I think there is a lot of information you are leaving out.
You are referencing a lot of columns that you never mentioned in your posts above.
It is very difficult to help you without a clear idea of exactly what the structure of each tab looks like.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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