How to select value from last column in a range

seanmccauley

Hi!

Would love to get feedback on how to create a "Current Status" formula that looks by row across columns to pull the latest value.  What I want is a formula that updates automatically as  I populate columns with weekly Account Status.  

Below is a screenshot that shows the columns I am working with.  The leftmost column is the Current Status column I am trying to populate.  It would look across both populated and un-populated columns to the right.  The goal is to capture the rightmost column that has a populated Account status.

Any suggestions?

Thanks!

Sean

 

Paul Newcome

Give this a try...

 

=VLOOKUP([Original Status]@row, [Original Status]@row:[Status 8-30-18]@row, COUNT(Original Status@row:[Status 8-30-18]@row))

 

The bold portion will be the name of the very last column. I just used the last column shown in your screenshot.

 

What this does is looks for the value of what is in your Original Status Column and hits against a table. That table is the [Original Status]@row:[Status 8-30-18]@row portion which says to look across the column range in whatever row the formula is in. If it finds the value (or course it will since the value is based off of whatever is in the first column of your table), then it will return the value that is in x number of columns to the right. To determine that x number, you use a COUNT function which counts only non-blank cells. As long as each week up to current has a value in it and each week after is blank, the COUNT function will determine how many columns to the right in that row are not blank. That number is used to determine how far over to the right your VLOOKUP will go for it's return value.

 

Basically you are saying "Since the value to look for matches, go over to the right however many cells are populated and return that value."

 

Make your Current Status column  a RYG type, and it should work just fine for you.

Desertbird

I'm not the OP but this helped me with my project, thank you Paul!