View original (French)

SEARCHV 9^9 and "zz"

Solved
stratiforme Posted messages 10 Status Member -  
stratiforme Posted messages 10 Status Member -
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 answers

  1.
    Frenchie83 Posted messages 2254 Status Member 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
    1. stratiforme Posted messages 10 Status Member
       
      Hello Frenchie,
      Complex formula but it works, that's what matters!
      Thank you very much.
      0
    2. stratiforme Posted messages 10 Status Member
       
      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
    3. Frenchie83 Posted messages 2254 Status Member 339
       
      Hello
      Could you provide some examples that do not work?
      0