Simplify and optimize a complex series of vlookups

[dhsilv2]

Hi all,

I have a bit of an issue and thought this might be a good place to get some ideas. I have a set of data with client ID going vertically down and then horizontally I have the date the client moved to a certain status. I want to know at any given time how many clients are in each status. I also have other items here, but for now that's my only goal and ultimately I want to graph it.

Now I couldn't come up with a way to write a single formula to calculate the count for each day, perhaps there's a way to do that?

Client ID | Application | Document Gathering | Approving | Complete | Withdrawn | Declined
1 | 12/31/2010 | 1/5/2011 | | | | 1/15/2011
2 | 1/15/2011 | 1/23/2011 | 1/23/2011 | 1/25/2011 | |

Above is a simple example of how the data looks.

Now I did come up with a way to do a test for each loan to see what day they are in a give status:

=vlookup(
MAX(
IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,9,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,9,0))<=CFP$1,1,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,10,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,10,0))<=CFP$1,2,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,11,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,11,0))<=CFP$1,3,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,12,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,12,0))<=CFP$1,4,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,13,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,13,0))<=CFP$1,5,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,14,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,14,0))<=CFP$1,6,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,15,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,15,0))<=CFP$1,7,0)
,IF(if(VLOOKUP($A55252,Data!$A$1:$P$63625,16,0)=0,100000,VLOOKUP($A55252,Data!$A$1:$P$63625,16,0))<=CFP$1,8,0)
)
,Status,2,0)


This works and I just end up with "end status" growing but I am not concerned with the end cases, just the cases leading up to complete, declined, or withdrawn. The logic is that I have a numerical value for each status, calculated the max value which is the current status and then reference a "status table" to define the numerical values.

However this is ultimately make creating a table to do then do a quick countifs and get my results vs. being able to do this in one place. Is there optimally a way to write a formula that will calculate the status based on the table logic above or a way to write that mess of vlookups (I have a feeling sumifs is a better choice than vlookup but I'm not sure it's that much faster) in a form that is not going to overwhelm my computer?

Is there a way to can either make the above much simplier

 

[V_Malkoti]

From your explanation I understand that you want to find out the latest status of each application.
I'm assuming that current status of an application can be determined by the most recent date in that row. So I created a column to the right and entered this formula which gets max date in the row, finds out its column number and then gets status text from the header row:
=INDEX(($R$1:$W$1,$R2:$W2),1,MATCH(MAX(R2:W2),R2:W2,0))

[TABLE="class: grid, width: 671"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Application[/TD]
[TD]Document Gathering[/TD]
[TD]Approving[/TD]
[TD]Complete[/TD]
[TD]Withdrawn[/TD]
[TD]Declined[/TD]
[TD]CurrentStatus[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12/31/2010[/TD]
[TD="align: right"]1/5/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/15/2011[/TD]
[TD]Declined[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1/15/2011[/TD]
[TD="align: right"]1/23/2011[/TD]
[TD="align: right"]1/23/2011[/TD]
[TD="align: right"]1/25/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD]Complete
[/TD]
[/TR]
</tbody>[/TABLE]

I believe you should then be able to get the summary using a pivot table or plot a graph using this additional column.

 

[dhsilv2]

From your explanation I understand that you want to find out the latest status of each application.
I'm assuming that current status of an application can be determined by the most recent date in that row. So I created a column to the right and entered this formula which gets max date in the row, finds out its column number and then gets status text from the header row:
=INDEX(($R$1:$W$1,$R2:$W2),1,MATCH(MAX(R2:W2),R2:W2,0))

[TABLE="class: grid, width: 671"]
<tbody>[TR]
[TD]Client ID[/TD]
[TD]Application[/TD]
[TD]Document Gathering[/TD]
[TD]Approving[/TD]
[TD]Complete[/TD]
[TD]Withdrawn[/TD]
[TD]Declined[/TD]
[TD]CurrentStatus[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12/31/2010[/TD]
[TD="align: right"]1/5/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1/15/2011[/TD]
[TD]Declined[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1/15/2011[/TD]
[TD="align: right"]1/23/2011[/TD]
[TD="align: right"]1/23/2011[/TD]
[TD="align: right"]1/25/2011[/TD]
[TD][/TD]
[TD][/TD]
[TD]Complete[/TD]
[/TR]
</tbody>[/TABLE]

I believe you should then be able to get the summary using a pivot table or plot a graph using this additional column.

Thank you for your help.

However I need more than the highest date. I need to know what status the loan was in on any given day.

So for client 1 I'd want to know on January 4th it is still in the application status because it is not yet in gathering.

Now I want one of two solutions. The ultimately goal is to get a count of clients in a given status on a given day, and to calculate that for the past 5 years (so about 2,000 days) and then to chart it. I'll also want to do some trend work but all that is fairly simple once I have the data.

My first solution was to build a grid with client and the status for each given day for that 5 year period (which is what the series of vlookups did). The vlookups were making a reference to the client, pull the date, and then I test the status on the date reference in the cells. I numerically defined each status 1-8 and then looked for the max status on the given day. That was the status it was in. Hopefully that makes more sense.