VLOOKUP returning false?

[Hayves]

I'm trying to grab the last cell value with a number in it from a column with a bunch of text as well. So by searching the internet I came up with

=VLOOKUP(9.9999999E+307,C:C,1)

Where C is the column I'm searching in. I know there's 3 cells that it should be skipping with text in them, then a whole bunch of blank ones, then it should find and return me a number. But instead it just says false! I think all my arguments are filled in the formula so I have no idea.

Any ideas?

 

[VoG]

Try

=LOOKUP(9.9E+307,C:C)

 

[T. Valko]

I'm trying to grab the last cell value with a number in it from a column with a bunch of text as well. So by searching the internet I came up with

=VLOOKUP(9.9999999E+307,C:C,1)

Where C is the column I'm searching in. I know there's 3 cells that it should be skipping with text in them, then a whole bunch of blank ones, then it should find and return me a number. But instead it just says false! I think all my arguments are filled in the formula so I have no idea.

Any ideas?

Try it like this...

=LOOKUP(1E100,C:C)

That will return the last (bottom-most) numeric value from column C.

 

[Jonmo1]

Welcome to the board...

Possibly you have transition options checked..

Tools - Options - Transition
Uncheck "Transition formula evaluation"


Hope that helps.

 

[Hayves]

Welcome to the board...

Possibly you have transition options checked..

Tools - Options - Transition
Uncheck "Transition formula evaluation"


Hope that helps.

Thanks, I tried all the suggestions in here but all return false. That option is also unchecked.

A lot of my data in this sheet is grouped, would that affect this at all? I also just tried moving the bottom-most text out of the column so there is no more text below the last value, and it's still showing as false.

 

[Yahya]

try this array formula Entered with Ctrl+Shift+Enter

=INDEX(C:C,MATCH(9.99999999999999E+307,C:C))

 

[Hayves]

try this array formula Entered with Ctrl+Shift+Enter

=INDEX(C:C,MATCH(9.99999999999999E+307,C:C))

That definitely worked, thanks! I'm wondering why that one worked and none of the others did though?

 

[NORMA_YANEZ]

How do i post thread?

 

[Jonmo1]

That definitely worked, thanks! I'm wondering why that one worked and none of the others did though?

I am wondering that as well...

Does the last formula work if you enter it without pressing CTRL + SHIFT + ENTER ?
Because it is definately not required.

Also, do the previous formulas work if you do enter them with CTRL + SHIFT + ENTER ?


Is your post a simplified example of something more complex?
If so, post your ACTUAL complete formula that is not working as you expect it to...



Can you use Excel Jeanie to post sample data sets and formulas?
See my signature for a link to the jeanie.

 

[Hayves]

I am wondering that as well...

Does the last formula work if you enter it without pressing CTRL + SHIFT + ENTER ?
Because it is definately not required.

Also, do the previous formulas work if you do enter them with CTRL + SHIFT + ENTER ?


Is your post a simplified example of something more complex?
If so, post your ACTUAL complete formula that is not working as you expect it to...



Can you use Excel Jeanie to post sample data sets and formulas?
See my signature for a link to the jeanie.

I don't have the proper permissions to install programs here at work unfortunately.

Something weird is going on because originally the formula didn't work if it wasn't in array form, but now that I test it again it does work. As do the rest of the formulas. Pretty strange.