Twiggy
New Member
Hi Excel Experts,
I've attached the file Test.xls. It's a copy of my production file with one row in it. The actual file has about 40,000 rows in each sheet.
There are two sheets in Test.xls. The first sheet is Data_File, where I'll store the results. The second sheet, Import_FIle, that contains the records I'm importing from Access. I run a cross-tab in Access to get the totals per week.
I’m using the criteria in the sheet Data_File:A11&C11&E11&N9 to find the total for week 2016*01 in the corresponding record in the Import File.
Expected Result: Data_File:N11=135.
The challenge is that I need a formula to do a vertical and a horizontal lookup without significantly slowing down performance. I considered using Index Match Match, but it looks like I’d need to list the entire array. I think that’ll be too large since my production file already has 40,000 rows. I appreciate any suggestions you can provide. Please let me know if I need to clarify.
Thanks in advance for your time.
I've attached the file Test.xls. It's a copy of my production file with one row in it. The actual file has about 40,000 rows in each sheet.
There are two sheets in Test.xls. The first sheet is Data_File, where I'll store the results. The second sheet, Import_FIle, that contains the records I'm importing from Access. I run a cross-tab in Access to get the totals per week.
I’m using the criteria in the sheet Data_File:A11&C11&E11&N9 to find the total for week 2016*01 in the corresponding record in the Import File.
Expected Result: Data_File:N11=135.
The challenge is that I need a formula to do a vertical and a horizontal lookup without significantly slowing down performance. I considered using Index Match Match, but it looks like I’d need to list the entire array. I think that’ll be too large since my production file already has 40,000 rows. I appreciate any suggestions you can provide. Please let me know if I need to clarify.
Thanks in advance for your time.
