# Simplify and optimize a complex series of vlookups

#### dhsilv2

##### Board Regular
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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))

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

<tbody>
</tbody>

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

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))

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

<tbody>
</tbody>

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

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.

Last edited:

Replies
0
Views
539
Replies
0
Views
462
Replies
2
Views
841
Replies
0
Views
728
Replies
0
Views
354

### Forum statistics

1,196,306
Messages
6,014,565
Members
441,828
Latest member
cofracr ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back