Find last value in an Excel row which is not NA

[Chan_Tze_Leong]

I am trying to find the last value in an Excel row which has figures and NA. I have used Match and Lookup but it does not show 93% (which is the last figure). 

NA

NA

87%

NA

NA

93%

NA

-

NA

NA

NA

NA

1200%

100%

1200%

5

0.08

[#2 · Accepted solution]

@Chan_Tze_Leong 

Kind of useless to attach an Excel file that we cannot modify or look at details because it password protected.  (Klunk!)

And in the future, you should show us the formulas that you tried that did not work.  It might be more educational for you.

=LOOKUP(1E+300, D34:Z34) works for me.  It returns 0.93, which we can format as 93%.

1E+300 is any large number -- whatever is easy for you to remember.  Another reasonable choice is 1E+100.

Some people use 9.99999999999999E+307.  Totally unnecessary!   That is the largest value that we can enter manually.  But it is not the largest value that Excel can calculate.

(1.79769313486232E+308 is, approximately.  We can calculate the exact value with the expression 10*1.79769313486231E+307 + 5.79E+293.  Again, totally unnecessary, since it is not likely that you will use such large values.)

[#3]

Thank you, Joe User, it worked. My apologies for the password logged file. I used =MATCH(2,1/(S34:AD34<>"NA"), ROW(S34:AD34)).

[#4]

@Chan_Tze_Leong 

I'm glad the LOOKUP(1E+300,...) trick works for you.  It is not intuitive.

Re: =MATCH(2,1/(S34:AD34<>"NA"), ROW(S34:AD34))

That does not make any sense to me at all.  The third parameter of MATCH should be -1, 0 or 1 to determine the type of match.  See the help page for details.  Moreover, MATCH returns a relative row or column number (e.g. 1 for S34, 2 for T34 etc), not the value that it matched, like 93%, which is what you want.

I suspect that you have a different function in mind; perhaps HLOOKUP.  The third parameter is a relative column number to determine what cell value to return in the matched column.  But even then, your usage does not make any sense to me.  ROW(S34:AD34) always returns 34 (the row number), which is not likely to be the relative column number that you want.

Oh well, you know it does not work.  I hope the comments above help you understand why not.  When problems arise, the first thing to do is:  read the help page to be sure that you understand the parameters of the functions that you use.