Match last non-blank within multiple lookup values

[cacos]

Hi everyone!


Tricky one here:


I have a list of weeks, and for each an amount of employees. Like this:

[pre]

Week1 Week2 Week3 Week4 Week5
100   200   150   300

[/pre]

I have output values that can be multiple weeks, lets say Week2, Week3, Week5.


I need to be able to fetch the last non-blank value, based on that selection of Weeks, which in this case would be 150 since Week5 is empty.


Thanks!

 

[jason]

while week 5 is empty, week 4 is not..... so wouldnt that be your last non-blank value?


maybe provide some more examples?

how is 'selection of weeks' going to be made/set?

 

[cacos]

That's right, but the user will select a "range of weeks", for example, Week2, Week3 and Week4


So you'll get an output range with the different weeks the user selected. So in this case, if user selected Week2, Week3 and Week4, the last value is 300.

 

[jason]

and how are they 'selecting' the weeks? highlightly the cells, drop down lists, filter?