Get the amount from one sheet using two criteria of another

Mikahl

New Member
Joined
Jul 15, 2012
Messages
8
Hi guys,

I am trying to get the amount from one sheet to populate into cells in a second sheet using two criteria from the second sheet:

Rows = Account number
Columns = Dates

The first sheet is a sales log that lists the date, account number, and the amount. I want to use columns with dates to compare against rows with acct numbers from the second sheet to get the amounts. I was going to combine vlookup and hlookup but it started to get ridiculous rather quickly. I've searched around and it looks like index and match would work. But I don't know how to do that with two sheets.

I have attached the example.

Thank you very much for any help you can offer.


Sheet1

DATEACCT DEBIT
7/5/20121 63,838.45
7/5/20121 51,968.36
7/5/20127 26,598.81
7/2/20125 26,340.81
7/6/20123 25,462.57
7/2/20128 25,203.78
7/9/20122 24,843.50
7/10/20122 24,711.19
7/3/20122 24,630.80
7/4/20129 24,594.51
7/4/20128 24,561.19
7/5/20127 24,534.53
7/6/20127 24,502.24
7/9/20126 24,410.81
7/7/20126 24,347.97
7/3/20126 24,027.44
7/2/20121 23,923.56
7/3/20124 23,195.19
7/9/20123 23,022.71
7/5/20128 22,867.72
7/4/20129 22,864.61
7/5/20122 22,796.41

<tbody>
</tbody>


Sheet2

ACCT7/2/20127/3/20127/4/20127/5/20127/6/2012
1
2
3
4
5
6
7
8
9

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I've searched around and it looks like index and match would work. But I don't know how to do that with two sheets.

Do you mean that you have trouble with the syntax for multiple sheets?
In that case, you can use the mouse to select the ranges, Excel will add the sheet names for you.

By the way, why not using a quick pivot table, and afterwards a copy/paste values if needed.
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Sheet2

*ABCDEF
1ACCT 07/02/201207/03/201207/04/2012#########07/06/2012
2123923.5600115806.810
32024630.8022796.410
43000025462.57
54023195.19000
6526340.810000
76024027.44000
8700051133.3424502.24
9825203.78024561.1922867.720
1090047459.1200

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:100px;"><col style="width:81px;"><col style="width:97px;"><col style="width:72px;"><col style="width:76px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B2=SUMPRODUCT(--(Sheet1!$A$2:$A$23=B$1),--(Sheet1!$B$2:$B$23=$A2),--(Sheet1!$C$2:$C$23))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Excel 07/10
=SUMIFS(Sheet1!$C$2:$C$23,Sheet1!$A$2:$A$23,B$1,Sheet1!$B$2:$B$23,$A2)
 

Mikahl

New Member
Joined
Jul 15, 2012
Messages
8
I just have problems. My classroom for excel has consisted of looking at other formulas people have written in the office and google searches. I'm a novice at best. I understand syntax for multiple sheets but in my spread for work I am picking out 17 Accounts receivible out of 2,000 to make them ineligible for lending purposes (factoring/asset based lending) I need to find the amounts of the invoices for these accounts on particular days. I will also look for the amounts paid in the same way from another spread. What would be my best course of action?
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Try Robert's approach above.
 

Mikahl

New Member
Joined
Jul 15, 2012
Messages
8
Thanks Robert. That looks like what I need. I really do appreciate the assistance. I'll reply with my success or failure shortly.
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

Thanks Robert. That looks like what I need. I really do appreciate the assistance. I'll reply with my success or failure shortly.



You are welcome. This will work for one sheet.If you need assistance with multiple sheets you may need to look here


Bear in mind that you data must be in the same layout on all sheets
 

Mikahl

New Member
Joined
Jul 15, 2012
Messages
8
Are you saying that I can not pull the information from sheet1 to sheet2? Or if I have a sheet3 also, I would need to use the syntax from your link?
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Are you saying that I can not pull the information from sheet1 to sheet2? Or if I have a sheet3 also, I would need to use the syntax from your link?

You can from Sheet1 into Sheet2, BUT if you have more worksheets that you need do consolidate you need approach(or similar) from the link I posted.
 

Mikahl

New Member
Joined
Jul 15, 2012
Messages
8
Excel is giving me an error stating that a value in my formula is of the wrong data type. I am really showing my ignorance here. However, I have gone though the data on sheet2 to ensure that my dates are dates, my acct #'s are numbers, and my amounts are accounting.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,862
Messages
5,598,500
Members
414,243
Latest member
Shockpulsar

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
Top