Excel Index Formula

**Hessah** · 07-08-2011, 12:05 AM

We have one column of data e.g.

A
B
C
D
E

And we're trying to have a cell that looks up the last cell in that column and display what's in that last cell, in this case, E.

I looked up Google, and someone gave this formula:

=INDEX(B7:B26,MAX(IF(LEN(B7:B26)>0,ROW(B7:B26),0)) ,1)
(And hit Ctrl Shift Enter, not sure what that does, but it doesn't work if we just hit Enter.)

It was working on a normal spreadsheet, but for some reason it doesn't work in the work document.. it keeps returning 0.

Anyone have any clue what has to happen for that formula to work properly?

Or even just explain what Ctrl Shift Enter does?

EDIT: There are page breaks involved in this spreadsheet.. if that makes a difference..

**Hessah** · 07-08-2011, 12:18 AM

Ohh found another formula that will work too..

If anyone is interested to know.. this page is useful!

http://www.techonthenet.com/excel/qu...last_value.php

**Ralath** · 07-08-2011, 01:12 AM

VLOOKUP seems easier than INDEX.

I think INDEX is useful if you need to lookup both a row and a column. But even then, I think it's just easier to use a VLOOKUP and an HLOOKUP.

I think there was that one time when I had to use an INDEX function... but I can't remember why... ahaha

**Ralath** · 07-08-2011, 01:20 AM

Ahhh... And you need to put in the CTRL+SHIFT+ENTER when you have matrices.

**Hessah** · 07-08-2011, 01:23 AM

I think the LOOKUP formula requires you to sort the column in alphabetical order isnt it?

And that's for looking up a specific text?? We want it to show the last cell of a column...

**Ralath** · 07-08-2011, 01:26 AM

Originally Posted by Hessah

I think the LOOKUP formula requires you to sort the column in alphabetical order isnt it?

I think you don't have to if you put a 0 at the end (tells it to be an exact match).

Quote:

And that's for looking up a specific text?? We want it to show the last cell of a column...

You can change the last column to be whatever you want it to be. I just put in text for an example.

**Blaaaaaaaah** · 07-08-2011, 01:46 AM

this thread just reminded me I had a problem with the vlookup formula a while ago and I wanted to ask on FF but I completely forgot

eeeeeeeeeeeeeeeek

**Hraesvelg** · 07-08-2011, 01:49 AM

I'm disappointed in the Excel HHOOKUP command. Never returns what I want it to.

**Blaaaaaaaah** · 07-08-2011, 01:53 AM

asdfdfhjgfjgh LLOOL

**Ralath** · 07-08-2011, 02:05 AM

Originally Posted by Blaaaaaaaah

this thread just reminded me I had a problem with the vlookup formula a while ago and I wanted to ask on FF but I completely forgot

eeeeeeeeeeeeeeeek

The 0 at the end usually fixes most problems. 8/10 times.