Look-up formula

[chaosincarnate]

Silly question. I've done this in the past but can't remeber how now.


Have a tab in a workbook that i want to look up info in another tab. That information is only the last item inputted in a column. Basically looks at another page and gets the last thing that was inputted in specific column and puts it into the original tab cell.


Example.


Column "A" has in it:

1

1

2

3

4

4

5

Blank

Blank

Blank

Blank


need a way to refernce this column. there's 42 lines in the colum and most are blank. Just need to reference last item inputted and post that on a differnt tab of the workbook. Have no experince with macro's or VBA or anything like that.


Thanks ahead of time

 

[Luke M]

Array formula (confirm using Ctrl+Shift+enter):

=INDEX('Sheet 2'!A1:A100,MAX(IF('Sheet 2'!A1:A100<>"",ROW('Sheet 2'!A1:A100))))

 

[chaosincarnate]

got a 0 when i tested it out.


using Excel 2007


Had to shorten the to E44 lines. Have other data below that.


=INDEX('Level Progression'!E2:E44,MAX(IF('Level Progression'!E2:E44<>"",ROW('Level Progression'!E2:E44))))

 

[Luke M]

Index needs to callout at least row 1 (since it's being fed a row number. Otherwise it thinks A2 is 1st cell, and throws things off).


=INDEX('Level Progression'!E:E,MAX(IF('Level Progression'!E2:E44<>"",ROW('Level Progression'!E2:E44))))

 

[chaosincarnate]

Still getting a 0 for an answer...


I tried this:


=INDEX('Level Progression'!E2:E44, MAX('Level Progression'!E2:E44,ROW('Level Progression'!E2:E44)))


and got a 3 but should of been a 6. The evaluation starts with the "row" and gets a 2, then MAX gets a 6 (which is my highest number), then comes up with a 3 for an answer.

I don't see a division in the equation.


Going to tinker with this a bit more. The answer is right infront of me. I think the formula will work just have to figure out why its not.

 

[Luke M]

The 6 is going into the INDEX function. Which, since your formula still starts at E2, means it takes the 6th cell, aka, E7. If you want it to correctly pick up E6, you need to change that first array callout.