Victoria_excel
New Member
- Joined
- Dec 29, 2020
- Messages
- 2
- Office Version
- 365
- Platform
- 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:
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!!
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!!