Pull Data based on two criteria


Is it possible to pull data from sheet "SuspenseDB" tab "Aging Detail" from column C "Policy"that corresponds to "account" 2412100 in Column B to pull in what is in Column O "Sum of amount"

I also need the same thing done for accounts "2416300" and "2203400" based on column C "Policy" those accounts matched to pull in Column O "Sum of amount"

You will see in worksheet "Suspense" tab "Suspense" that "policy" will be in Column A and the corresponding accounts in column C for "2412100" - Column D for account "2416300" and column F for account "2203400" that will need Column O "Sum of amount" pulled in to the corresponding policy and account number. 



Selected Answer

Please try this formula in cell C2 of your worksheet Suspense in workbook Suspense.xlsx.


You can copy it to D2 and F2. Change the formula in E2 as follows. Then copy C2:F2 down as far as you need.


The recurring function ADDRESS(2,3,1,1,$M$1) specifies C2 in the other workbook. 2 is the row, 3 is the column. Accordingly, ADDRESS(2,15,1,1,$M$1) specifies O2. M1 holds the workbook & sheet name (you can move it to any other location on the same worksheet). I have the following formula in M1.

="[" & M2 &" " & M3 & ".xlsx]" & M4

M2 = Athene SuspenseDB Aging v3 (the workbook's name)
M3 = '11262018 (the date). The cell format is General. If you format it is Text the leading apostrophe isn't required.
M4 = Aging Detail (the worksheet name)
You can change all these details as required. The formula in M1 will produce a proper address from the parts. The purpose of this arrangement, apart from shortening the formula in C2, is to enable you to easily change the date.

The function OFFSET(INDIRECT(ADDRESS(2,3,1,1,$M$1)),0,0,10000) specifies a range with 10,000 rows (single column) starting from C2. Accordingly, OFFSET(INDIRECT(ADDRESS(2,15,1,1,$M$1)),0,0,10000)  specifies a single column range of 10,000 rows starting from O2. When you change the range size make sure to change it for all three ranges specified in the formula.

Finally, Suspense!C1:D1 and F1 have a General format whereas the account numbers in the other sheet (column D) are formatted as Text. If you format C1:F1 also as Text the formula could make the comparison without converting the format.



I apologize. I am not trying to Sum anything.

On sheet "Suspense" for example in 2412100 cell reference C2:C6404 I am trying to reference the policy number in A2 to match in sheet "Athene DB Aging" Tab "Aging Detail" - I need to match to Column C, Column D, and Column O but pull out the unique value in Column O that corresponds to the policy number that matches the 2412100 account and the amount that is referenced in Column O. (I will need to do the same on "Suspense" sheet for account 2416300 in cell reference D2:d6404 and for account 2203400 in cell reference f2:f6404

Would a index formula work best in this scenario? 

I've been separating each account into separate tabs and using Vlookup to pull out the amount in column O but I have to do it daily and it takes a lot of time. (=VLOOKUP(TRIM(A2),'[Athene_SuspenseDB_Aging_v3_11262018.xlsx]2412100'!$C:$O,13,0) - Gives me the result to match Policy to Amount in Column O but I also need to reference the specific account number. 

This would save me so much time. Please and thank you so much!
Sroncey21 (rep: 28) Nov 27, '18 at 10:15 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login