# Multiple Criteria Formula with Different Worksheets

#### Victoria_excel

##### New Member
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:
 Date Deposit Credit Deposit Cash Deposit Other 1/01/20 100 1/01/20 200 1/01/20 300

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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Victoria_excel

##### New Member
=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.

Replies
3
Views
31
Replies
1
Views
168
Replies
0
Views
221
Replies
1
Views
129
Replies
1
Views
173

1,129,392
Messages
5,636,044
Members
416,893
Latest member
Jomider

### 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.

### Which adblocker are you using?

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

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