View original (French)

SEARCHV 9^9 and "zz"

Solved
stratiforme Posted messages 10 Status Membre -  
stratiforme Posted messages 10 Status Membre -
Hello,
everything is in the title... or almost!
I can use either of the formulas
for example:
in C1 I write:
=VLOOKUP(9^9,P:P,1) when I am looking for the last value in the last cell of the column (here P)
same when it comes to my case of "-" I use "zz" instead of "9^9".
My concern is that in my searches I also have columns that contain both in the same column, depending on the daily update.
Is there a formula/a macro that allows me to combine the two formulas?

so that in C1 I get a number or a letter (or my famous - (dash)) as the last written value

Best regards

Configuration: Windows 7 / Firefox 40.0

4 réponses

Réponse 1 / 4
Frenchie83 Posted messages 2254 Status Membre 339
 
Hello
Try this
=IF(MATCH(VLOOKUP("ZZ",P:P,1,1),P:P,0)>MATCH(VLOOKUP(9^9,P:P,1,1),P:P,0),VLOOKUP("ZZ",P:P,1,1),VLOOKUP(9^9,P:P,1,1))
Best regards
0
stratiforme Posted messages 10 Status Membre
 
Hello Frenchie,
Complex formula but it works, that's what matters!
Thank you very much.
0
stratiforme Posted messages 10 Status Membre
 
Re-hello
I should have tested this formula for a longer time and with more data; it works randomly and inconsistently
The column must start with a letter (or a dash), otherwise nothing as long as it is not in the update
Example:
Day 1: 10
Day 2: 5
Day 3: -
I should wait until day 3 to get a data point; days 1 and 2 didn't exist
And even if my column starts with a letter, it is not taken into account; the data will only be considered from the following number... etc...
In short, unusable
If someone has another idea, I'm all ears

Best regards
0
Frenchie83 Posted messages 2254 Status Membre 339
 
Hello
Could you provide some examples that do not work?
0