#ERROR in Lookup function

[matthewlouis]

Hello Experts,

In looking up the last number in a column, I am using =IFERROR(LOOKUP(2,1/(Q3:Q62<>""),Q3:Q62),""). It's correctly stating the right number in each column (if there is a number).

However, when there is NOT a number in the column, I get the #ERROR message with that IFERROR formula (?) So I need a formula to return BLANK if there is not a number in the column because it determines another calculation if it is blank.

What would be the formula to return an empty cell if there is no number in the column? What am I missing in my formula?

Thanks in advance!!

 

[JoeMo]

The formula you posted should be returning the contents of the last filled cell in the specified range, whether it is text or a number. It will return "" if the specified range is empty.

If you want the last number in that range try:

=IFERROR(LOOKUP(1E+307,Q3:Q62),"")

 

[sheetspread]

Is this connected to a database? I've never heard of Excel returning #ERROR though it's common in Access, SSRS, Sharepoint, etc.

 

[matthewlouis]

Joe, your formula returned a blank (?) and there was a number in the column.

 

[matthewlouis]

Yes, I use a database called xlq that returns stock prices based on the date

 

[matthewlouis]

OK, think I have it now.

=IFERROR(LOOKUP(2,1/(ISNUMBER(Q3:Q63)),Q3:Q63),"")

Thanks for the help!!!

 

[Marcelo Branco]

Joe, when I said there was a number in the column, I put your formula in another columns where there IS a number and it returned a blank.

Sorry, hope that communicates it better.

So it seems the "numbers" in your range are texts, not real numbers
To check, try
=ISNUMBER(Q3) and copy down

M.

 

[Aladin Akyurek]

OK, think I have it now.

=IFERROR(LOOKUP(2,1/(ISNUMBER(Q3:Q63)),Q3:Q63),"")

Thanks for the help!!!

In that case...

=IFERROR(LOOKUP(9.99999999999999E+307,1/Q3:Q63,Q3:Q63),"")

 

[matthewlouis]

Actually, this is what worked . . .

=IFERROR(LOOKUP(2,1/(ISNUMBER(Q3:Q63)),Q3:Q63),"")

Thanks for all the help

 

[Aladin Akyurek]

Actually, this is what worked . . .

=IFERROR(LOOKUP(2,1/(ISNUMBER(Q3:Q63)),Q3:Q63),"")

Thanks for all the help

This will skip text numbers and logical values (like TRUE) in Q3:Q63, while

=IFERROR(LOOKUP(9.99999999999999E+307,1/Q3:Q63,Q3:Q63),"")

won't.

If there are no text numbers or logical values, both formulas will work the same way.