Multiple Criteria Formula with Different Worksheets

Victoria_excel

New Member
Joined
Dec 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,

So I've been trying to figure out what formula I could use between VLOOKUP and Match/Index I think I finally found a formula that would work, unfortunately I cannot get it to work when using data from another worksheet.

{=INDEX('[2020 Balance Report.xlsx]Dec'!$F$82:$F$170,MATCH(1,(F6='[2020 Balance Report.xlsx]Dec'!$A$21:$A$160)*(F7='[2020 Balance Report.xlsx]Dec'!$D$17:$D$160),0))}

The bolded cells come from a different worksheet than the others. The bolded cells worksheet is the worksheet this formula would be on.
When I place the data I need onto the same worksheet the formula works, but as soon as I change it to the new worksheet it gives me #N/A

I have to take specific data from the 2020 balance report and place into a new spreadsheet so I'm trying to create a formula so I don't have to retype everything. I was using VLOOKUP until I realized it doesn't take into account multiple criteria. (there are multiple rows with the same date, VLOOKUP only takes the first row with the matching date)

For example in the balance report i'll have:
DateDeposit CreditDeposit CashDeposit Other
1/01/20100
1/01/20200
1/01/20300

I need a formula that will allow me to take all the data for 1/01/20 and put it into the same row, into a different spreadsheet automatically, without having to change anything on the balance report spreadsheet.

Thank you for any and all help!!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Victoria_excel

New Member
Joined
Dec 29, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
=IFERROR(INDEX('[2020 Balance Report.xlsx]Dec'!$F$82:$F$119,AGGREGATE(15,6,(ROW('[2020 Balance Report.xlsx]Dec'!$A$76:$A$112)-ROW('[2020 Balance Report.xlsx]Dec'!$A$76)+1)/('[2020 Balance Report.xlsx]Dec'!$A$76:$A$112=F6),COLUMNS($D$9:$D9))),"")

I've now also tried this formula with no luck, I get 0.00 but unsure where it's pulling the "0.00" amount from.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,283
Messages
5,635,299
Members
416,851
Latest member
zeldadav

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