Find Last instance colum A and return corresponding B Value

[Will3001]

Hi
I have two columns of data one with numbers (columnA) and the other with
Y or N (column b)
What I'd like to do is find the last instance of a number in column A
and return the corresponding B value. Column A value can appear any
number of times.

e.g

Column A Column B
100 N
101 N
101 Y
102 N
103 N
103 N
103 Y
104 N
104 Y

Essentially for each of 100,101,102 etc I want to look at the last
instance of it in column A and return to my secified cell what the
corresponding B value is

All help appreciated.

Thanks
Will.




--
Will3001

[Claus Busch]

Hi Will,

Am Tue, 2 Jul 2013 12:29:17 +0100 schrieb Will3001:

> Column A Column B
> 100 N
> 101 N
> 101 Y
> 102 N
> 103 N
> 103 N
> 103 Y
> 104 N
> 104 Y

for 100 try:
=INDEX($B$1:$B$100,LOOKUP(2,1/($A$1:$A$100=100),ROW($1:$100)))
you also can change the value with a cell reference


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

[Will3001]

Hi Claus,

That appears to do the job nicely, thank you, will try it on a larger
data set later on..

Thanks again
Will.

Claus Busch;1612572 Wrote:
> Hi Will,
>
> Am Tue, 2 Jul 2013 12:29:17 +0100 schrieb Will3001:

> -

> > Column A Column B
> > 100 N
> > 101 N
> > 101 Y
> > 102 N
> > 103 N
> > 103 N
> > 103 Y
> > 104 N

> > 104 Y-

>
> for 100 try:
> =INDEX($B$1:$B$100,LOOKUP(2,1/($A$1:$A$100=100),ROW($1:$100)))
> you also can change the value with a cell reference
>
>
> Regards
> Claus B.
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

--
Will3001