LOOKUP VALUE OCCURS ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE

Hello All

Writing this workbook in 2007, but it will be used in 2003. Have
searched but can't find answer to this. I have a 5-column (A to E)
table array and I'm using VLOOKUP in several other cells to return
values in col2, 3, 4 & 5 with my lookup value being col 1. However,
col 1 will contain numbers which occur more than once. How can I get
VLOOKUP (or something else?) to pick the last occurence (lowest row)
and use that one? Also, the array will be sorted - smallest to
largest - as the rows go down - how will the SORT work on duplicate
entries? I'd like, if possible, for the entry made most recently to
be the lowest of the duplicate entries so that VLOOKUP (or whatever)
will pick that one to use. Will any kind of SORT do that?

This should do it for you:
=INDEX($B$1:$B$10,MATCH(MAX(IF($A$1:$A$10="A",ROW( $A$1:$A$10))),IF($A$1:$A$10="A",ROW($A$1:$A$10)),0 ))

Enter with Ctrl + Shift + Enter, not just Enter.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"robzrob" wrote:

Hello All

Writing this workbook in 2007, but it will be used in 2003. Have
searched but can't find answer to this. I have a 5-column (A to E)
table array and I'm using VLOOKUP in several other cells to return
values in col2, 3, 4 & 5 with my lookup value being col 1. However,
col 1 will contain numbers which occur more than once. How can I get
VLOOKUP (or something else?) to pick the last occurence (lowest row)
and use that one? Also, the array will be sorted - smallest to
largest - as the rows go down - how will the SORT work on duplicate
entries? I'd like, if possible, for the entry made most recently to
be the lowest of the duplicate entries so that VLOOKUP (or whatever)
will pick that one to use. Will any kind of SORT do that?
.

See if this is what you had in mind...

X...1
Y...5
Z...2
X...4

To lookup the *last* instance of X:

=LOOKUP(2,1/(A1:A4="x"),B1:B4)

Result = 4

--
Biff
Microsoft Excel MVP


"robzrob" wrote in message
...
Hello All

Writing this workbook in 2007, but it will be used in 2003. Have
searched but can't find answer to this. I have a 5-column (A to E)
table array and I'm using VLOOKUP in several other cells to return
values in col2, 3, 4 & 5 with my lookup value being col 1. However,
col 1 will contain numbers which occur more than once. How can I get
VLOOKUP (or something else?) to pick the last occurence (lowest row)
and use that one? Also, the array will be sorted - smallest to
largest - as the rows go down - how will the SORT work on duplicate
entries? I'd like, if possible, for the entry made most recently to
be the lowest of the duplicate entries so that VLOOKUP (or whatever)
will pick that one to use. Will any kind of SORT do that?

On Jan 15, 8:55*pm, ryguy7272
wrote:
This should do it for you:
=INDEX($B$1:$B$10,MATCH(MAX(IF($A$1:$A$10="A",ROW( $A$1:$A$10))),IF($A$1:$A$*10="A",ROW($A$1:$A$10)), 0))

Enter with Ctrl + Shift + Enter, not just Enter.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"robzrob" wrote:
Hello All

Writing this workbook in 2007, but it will be used in 2003. *Have
searched but can't find answer to this. *I have a 5-column (A to E)
table array and I'm using VLOOKUP in several other cells to return
values in col2, 3, 4 & 5 with my lookup value being col 1. *However,
col 1 will contain numbers which occur more than once. *How can I get
VLOOKUP (or something else?) to pick the last occurence (lowest row)
and use that one? *Also, the array will be sorted - smallest to
largest - as the rows go down - how will the SORT work on duplicate
entries? *I'd like, if possible, for the entry made most recently to
be the lowest of the duplicate entries so that VLOOKUP (or whatever)
will pick that one to use. *Will any kind of SORT do that?
.- Hide quoted text -

- Show quoted text -

Thanks - have got another formula now which works. (Can't see any
'Yes' (or no) to click - sorry

On Jan 15, 8:55*pm, ryguy7272
wrote:
This should do it for you:
=INDEX($B$1:$B$10,MATCH(MAX(IF($A$1:$A$10="A",ROW( $A$1:$A$10))),IF($A$1:$A$*10="A",ROW($A$1:$A$10)), 0))

Enter with Ctrl + Shift + Enter, not just Enter.

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''..



"robzrob" wrote:
Hello All

Writing this workbook in 2007, but it will be used in 2003. *Have
searched but can't find answer to this. *I have a 5-column (A to E)
table array and I'm using VLOOKUP in several other cells to return
values in col2, 3, 4 & 5 with my lookup value being col 1. *However,
col 1 will contain numbers which occur more than once. *How can I get
VLOOKUP (or something else?) to pick the last occurence (lowest row)
and use that one? *Also, the array will be sorted - smallest to
largest - as the rows go down - how will the SORT work on duplicate
entries? *I'd like, if possible, for the entry made most recently to
be the lowest of the duplicate entries so that VLOOKUP (or whatever)
will pick that one to use. *Will any kind of SORT do that?
.- Hide quoted text -

- Show quoted text -

Thanks - have got another formula now which works. (Can't see any
'Yes' (or no) to click - sorry

On Jan 15, 9:09*pm, "T. Valko" wrote:
See if this is what you had in mind...

X...1
Y...5
Z...2
X...4

To lookup the *last* instance of X:

=LOOKUP(2,1/(A1:A4="x"),B1:B4)

Result = 4

--
Biff
Microsoft Excel MVP

"robzrob" wrote in message

...



Hello All

Writing this workbook in 2007, but it will be used in 2003. *Have
searched but can't find answer to this. *I have a 5-column (A to E)
table array and I'm using VLOOKUP in several other cells to return
values in col2, 3, 4 & 5 with my lookup value being col 1. *However,
col 1 will contain numbers which occur more than once. *How can I get
VLOOKUP (or something else?) to pick the last occurence (lowest row)
and use that one? *Also, the array will be sorted - smallest to
largest - as the rows go down - how will the SORT work on duplicate
entries? *I'd like, if possible, for the entry made most recently to
be the lowest of the duplicate entries so that VLOOKUP (or whatever)
will pick that one to use. *Will any kind of SORT do that?- Hide quoted text -

- Show quoted text -

Thanks - have got another formula now which works.

Rob,
Please post the answer instead of posting cryptic messages. This will help
other users.

Cheers