Finding the 2nd Lookup Value when counter gets to Zero.

[Nomad33]

I have a table that has clients names and the number of sessions in their respective packages. In another table, every time that client's name is entered, it uses vlookup and takes away a session from their package (keeping the original number of sessions in the first table).

My problem comes when their sessions run out (sessions countdown to zero), and the client signs up for another package. When the vlookup finds the name in from the first table, it returns the first name that appears, and not the second which I need.

Attached is a simplified version of my full workbook, which focuses solely on this issue. The blue highlighted cell needs to return a value of '3' and not '-1' (first it needs to realize a 'Table 2' value associated with 'Kelly' is '0'. it then needs to look for the next 'Kelly' in 'Table 1', and the associated value of '4' in cell B10.)

I would like this to be able to work if 'Kelly' appeared in 'Table 1' an unlimited amount of times.

sample.xlsx

[protonLeah]

Please Login or Register  to view this content.

[Nomad33]

This is getting closer. I understand the formula provided searches from the bottom up, but the value in E9 from this example should be 3 (4-1). Subsequently, if D10="Kelly", then E10 must yield a value of '2' (4-1-1).

Thanks for the help thus far!

[kvsrinivasamurthy]

PL see the attached file with array formulas, can be dragged down.

[Nomad33]

Once a package in "table 2' gets down to '0', I need those 'Sessions Remaining' above them in 'table 2' to remain the same after adding a new package in 'table 1' under the same name.

In this example (looking at 'sample ANS1.xlsx'), E4 should = 3, E5=2, E7=1, E8=0, and E9=3.

Any ideas?

[kvsrinivasamurthy]

PL see attached file.
Formula is changed in E column.
Values in B column are changed.
Helper column C is used.

[Nomad33]

[SOLVED]

Works great. Thanks!