Hello

I am trying to find out if there is a way or work around to use vlookup in a
list where it finds the last occurance of an item and gives the corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value 1, but
the problem is the vlookup finds the first occurance only and returns 5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

One way. This is an ARRAY formula which must be entered using
Ctrl+shift+enter
=INDIRECT("c"&MAX(IF(B1:B10="apple",ROW(A1:A10))))

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hello

I am trying to find out if there is a way or work around to use vlookup in
a
list where it finds the last occurance of an item and gives the
corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value 1,
but
the problem is the vlookup finds the first occurance only and returns 5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

Hi

Do you know if this work with text too, or just numbers, as some of the data
are text.

Thank you.



"Don Guillett" wrote:

One way. This is an ARRAY formula which must be entered using
Ctrl+shift+enter
=INDIRECT("c"&MAX(IF(B1:B10="apple",ROW(A1:A10))))

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hello

I am trying to find out if there is a way or work around to use vlookup in
a
list where it finds the last occurance of an item and gives the
corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value 1,
but
the problem is the vlookup finds the first occurance only and returns 5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

On Thu, 7 Sep 2006 07:07:01 -0700, Nav wrote:

Hello

I am trying to find out if there is a way or work around to use vlookup in a
list where it finds the last occurance of an item and gives the corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value 1, but
the problem is the vlookup finds the first occurance only and returns 5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

You could use this **array** (entered with <ctrl<shift<enter ) formula:

=INDEX(B1:B10,MAX(ROW(A1:A10)*(A1:A10="Apple")))

With your fruit in A1:A10 and your values in B1:B5.


--ron

Did you try it?

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hi

Do you know if this work with text too, or just numbers, as some of the
data
are text.

Thank you.



"Don Guillett" wrote:

One way. This is an ARRAY formula which must be entered using
Ctrl+shift+enter
=INDIRECT("c"&MAX(IF(B1:B10="apple",ROW(A1:A10))))

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hello

I am trying to find out if there is a way or work around to use vlookup
in
a
list where it finds the last occurance of an item and gives the
corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value
1,
but
the problem is the vlookup finds the first occurance only and returns
5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

I tried it for both, but it didn't seem to work for either, not sure if I did
something wrong but I copied and pasted the below (with ctrl+shift+enter),
hence I was asking.

Thanks anyway.

"Don Guillett" wrote:

Did you try it?

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hi

Do you know if this work with text too, or just numbers, as some of the
data
are text.

Thank you.



"Don Guillett" wrote:

One way. This is an ARRAY formula which must be entered using
Ctrl+shift+enter
=INDIRECT("c"&MAX(IF(B1:B10="apple",ROW(A1:A10))))

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hello

I am trying to find out if there is a way or work around to use vlookup
in
a
list where it finds the last occurance of an item and gives the
corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the value
1,
but
the problem is the vlookup finds the first occurance only and returns
5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav

This will work for either text or numbers in col B that you seek. It will
give the item in col C.
Perhaps you did not Array enter. You must use ctrl+shift+enter instead of
just enter.
=INDIRECT("c"&MAX(IF(B1:B11=1,ROW(A1:A11))))
--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
I tried it for both, but it didn't seem to work for either, not sure if I
did
something wrong but I copied and pasted the below (with ctrl+shift+enter),
hence I was asking.

Thanks anyway.

"Don Guillett" wrote:

Did you try it?

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hi

Do you know if this work with text too, or just numbers, as some of the
data
are text.

Thank you.



"Don Guillett" wrote:

One way. This is an ARRAY formula which must be entered using
Ctrl+shift+enter
=INDIRECT("c"&MAX(IF(B1:B10="apple",ROW(A1:A10))))

--
Don Guillett
SalesAid Software

"Nav" wrote in message
...
Hello

I am trying to find out if there is a way or work around to use
vlookup
in
a
list where it finds the last occurance of an item and gives the
corresponding
value in relation to it.
Eg.

Pear 5
Apple 5
Orange 3
Plum 2
Apple 1

I need it to look at the last occurance of Apple and return the
value
1,
but
the problem is the vlookup finds the first occurance only and
returns
5.

Any ideas anyone.

Thank you in advance for any help.

Regards,
Nav