Return value from last row

[Jonathan9]

Hi there!

I'm struggling to with a problem. I have two columns; Column 1 contains an identifying value with has both unique and repeated values and Column 2 has unique values for each cell.

What I would like to do is to return the last value in the segment of the column 2 for each unique identifier.

I've attached a sample.

Appreciate the help!

[:) Sixthsense :)]

In F5 cell of Sheet1


=IFERROR(LOOKUP(2,1/($B$5:$B$39=E5),$C$5:$C$39),"")


Drag it down..

[XOR LX]

Hi.

Since your values in column B appear to be in ascending order, in F5:

=LOOKUP(E5,B$5:B$39,C$5:C$39)

Otherwise, if you cannot guarantee that order:

=LOOKUP(1,0/(B$5:B$39=E5),C$5:C$39)

though the first is much more efficient.

Regards

[Kaper]

Yet another - try such array formula*

Please Login or Register  to view this content.

and copy down
*)...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[XOR LX]

@Kaper

Volatile?

Array formula?

Why?

Regards

[AliGW]

Or in F5:

Please Login or Register  to view this content.

[Kaper]

@XOR LX
Right.
In this case - no real need.
But ... for some users it may be a bit easier to understand how it works. Well, I know that it is personal, but I know a number of users who have contignous problems with LOOKUP understanding, especially in form similar to LOOKUP(1,0/(lookuprange=lookupvalue),returnedvaluerange) .

[XOR LX]

@XOR LX
Right.
In this case - no real need.
But ... for some users it may be a bit easier to understand how it works. Well, I know that it is personal, but I know a number of users who have contignous problems with LOOKUP understanding, especially in form similar to LOOKUP(1,0/(lookuprange=lookupvalue),returnedvaluerange) .

Sure, but better to be non-volatile and do some studying than to be volatile yet aware!

Regards

[AliGW]

@XOR LX
Right.
In this case - no real need.
But ... for some users it may be a bit easier to understand how it works. Well, I know that it is personal, but I know a number of users who have contignous problems with LOOKUP understanding, especially in form similar to LOOKUP(1,0/(lookuprange=lookupvalue),returnedvaluerange) .

In my experience, people who want to understand the syntax of the formula will ask. Making assumptions about their ability to understand is not really our place when we are providing possible solutions ...

[Jonathan9]

This will do the trick!

Thanks for the help.

[Pepe Le Mokko]

@Kaper

Volatile?

Array formula?

Why?

Regards

Couldn't agree more. Array formulas do seem to be " cool" as they say nowadays, and then OP's come back later " My workbook ( with array formulas) is soooooooooooo slow..."

[XOR LX]

Couldn't agree more. Array formulas do seem to be " cool" as they say nowadays, and then OP's come back later " My workbook ( with array formulas) is soooooooooooo slow..."

Actually it's the volatility which is the issue here. Kaper's CSE construction will be no less slow to calculate than the non-CSE reciprocal LOOKUP construction, i.e.:

=LOOKUP(1,0/(B$5:B$39=E5),C$5:C$39)

In fact, I imagine if anything that the CSE set-up is actually quicker.

What people don't understand is that the above construction with LOOKUP is also an array formula. It simply does not require CSE to commit it. The same goes for SUMPRODUCT, AGGREGATE, etc., none of which perform any better than the equivalent CSE versions.

And the worse culprit of all is the sadly commonplace attempt to "avoid" array formulas by inserting one or more additional INDEX functions in appropriate places. Of course, this doesn't mean that it is not an array formula. It is, and in fact will take longer to calculate than the equivalent CSE version by virtue of the extra function (INDEX).

Regards