offset a vlookup

[agalinauskas]

I am trying to return a date based off of a wage threshold. My pivot table has given me something like this:

Column A are ID numbers for an employer. Column B are check dates and Column C are check amounts


Column A has many blanks because the ID is listed once and the other data belongs to that number

What I am trying to do is write a vlookup or index formula that can return the date that the check amounts added to 1,000 or more. I have tried many methods but I cannot get it to work. Any Ideas?

I was thinking an offset vlookup, but I need to be able to copy the formula down for 100's of employers and I am not sure if that will mess up when filling down.

[NBVC]

You can add a formula outside the Pivot table that puts the header in every row..

say you pivot table ID numbers starts in A12, then in a column of row 12 outside the table add formula:

=IF(A12="",B11,A12)

copied down.

Now you should have an ID in every row.

[Ace_XL]

Perhaps upload a sample worksheet with expected results?

[agalinauskas]

Sample.xlsx

On the first sheet is my data sample, on the 2nd sheet is what I would like the results to look like if the formula works correctly. Thanks!

[NBVC]

In D2 of the Data sheet:

=IF(A2="",D1,A2)

copied down

in B2 of Vlookup sheet:

=IFERROR(INDEX(Data!$B$2:$B$26,MATCH(1,INDEX((Data!$C$2:$C$26>1000)*(Data!$D$2:$D$26=A2),0),0)),"")

copied down

[Ace_XL]

Primitive solution with the aid of NBVC's helper column. See attached

[agalinauskas]

Great so then I just need to vlookup for column E using the employer ID. I will test this out with my real data. Thanks

---------- Post added at 08:44 AM ---------- Previous post was at 08:40 AM ----------

I just realized the vlookup will still pull only the first instance...the problem is there will be close to 1,000 employers so I will need to get all those dates in one column. any ideas on how to do this?

---------- Post added at 08:50 AM ---------- Previous post was at 08:44 AM ----------

NBVC, that worked well however employer 3 should have returned 4/25 it returned 4/30. But this is close. Thanks so much!

[NBVC]

As I understand the question, you want to know the first date for each employer that is greater than 1000... is this incorrect? Why should it be 4/25 for Employer 3?

---------- Post added at 09:01 AM ---------- Previous post was at 08:58 AM ----------

If you are looking for when the cumulative sum is greater than 1000, then add yet another helper to column E of Data with formula in E2:

=SUMIF(D$2:D2,D2,C$2:C2)

copied down

Then revise vlookup formula to:

=IFERROR(INDEX(Data!$B$2:$B$26,MATCH(1,INDEX((Data!$E$2:$E$26>1000)*(Data!$D$2:$D$26=A2),0),0)),"")

copied down

[agalinauskas]

480+587 = 1067

---------- Post added at 09:03 AM ---------- Previous post was at 09:02 AM ----------

Yes I apologize it needs to be whet he combined amount is over 1000. Thanks!

---------- Post added at 09:03 AM ---------- Previous post was at 09:03 AM ----------

Yes I apologize it needs to be when he combined amount is over 1000. Thanks!

[NBVC]

See my updated post above....

[agalinauskas]

Thank you so much you are the best!, now if I am to do this on let say over 1000 rows I just need the range to go to the last row correct? Thanks again you have been so helpful!

[NBVC]

Yes, that is correct.

[agalinauskas]

So I just did this and it worked well however a few came back blank. I am not sure what caused it but I will see if I can figure out the logic. I can try to submit a sample to you but I need to make sure I take out any personal data.

[NBVC]

Yes, you can attach sample if you like here...

[agalinauskas]

sample2.xlsx

The errors are on the data tab starting at b87 and then you can see more after that. I guess its an error as it is returning a blank

[NBVC]

You don't have the formula in column E of the Data sheet that gets the cumulative sum...

And actually, that first formula I gave is not very efficient, try instead in Data Sheet, E2:

=IF(D2=D1,SUM(C2,E1),C2)

copied down...

Then formula in Sheet1 turns to:

=IFERROR(INDEX(Sheet1!$B$1:$B$16666,MATCH(1,INDEX((Sheet1!$E$1:$E$16666>$B$1)*(Sheet1!$D$1:$D$16666=A2),0),0)),"")

[agalinauskas]

i did forget that part shoot. OK let me try again

---------- Post added at 03:35 PM ---------- Previous post was at 03:17 PM ----------

It seems to just pull the first date and not the 1,000 date. could we start from the beginning? I know I seem like a noob but I am pretty good with excel. lets start from step 1 where on the first sheet I have the employer numbers and on the 2nd sheet I have the ID check date and total in a-c with nothing yet in d and e. Thanks.

[NBVC]

See sample attached.

[agalinauskas]

OK this worked thanks so much This is a huge help. Take care!

[Cutter]

@ agalinauskas

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.