List of accounts if match date

[richmcgill]

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]

 

[Joe4]

You should be able to do it with a SUMIFS formula, where you are summing the amount from the amount column when the Loan No matches and the date matches.

See: https://exceljet.net/excel-functions/excel-sumifs-function

 

[richmcgill]

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!

 

[Joe4]

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?

 

[richmcgill]

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.

 

[Joe4]

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.

 

[richmcgill]

I see index match formulas working in other very similar scenarios. I guess I will try on my own. Thanks.

 

[richmcgill]

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

This is one solution.

 

[Joe4]

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.