VLOOKUP - how to get last found matching value??

[wnstar21]

I am trying to find out a way for VLOOKUP to return the specified value of a match, but the thing is, the match is listed more than once in the first column.

example:

........A........B........C................H...............J
1.....MIKE....7........99............JOHN .........In J1, I would put =VLOOKUP(H1,A1:C6,3,FALSE)
2.....JOHN....8.......77
3.....DAN.....15......65
4.....RON.....11......44
5.....JOHN....12.....39
6.....ERIC.....8.......39
7....JOHN....17.......45


With the exact match, the formula should return '77'. What would I do if I wanted it to return the value in the third column for the 'JOHN' listed? I am going to continue to add names, going down the first column, so I would want the number for the last time I entered the name.

Is there a function that can start from the bottom and look up?

Or a formula that can start at specified point and work up? The specific point could be something that I would never reach, like row 3000, and that could guarantee I found the last one listed.

PLEASE HELP!!!

[NBVC]

tRY:

=LOOKUP(2,1/($A$2:$A$3000=H1),$C$2:$C$3000)

[wnstar21]

that works great!

Can I ask, what does the '2,1/' mean in the beginning of the equation?

[NBVC]

This part ($A$2:$A$3000=H1) evaluates to an array of TRUEs/FALSEs. 1 divided be each of these evaluates to an array of 1's and #DIV/0! errors (because 1/True is like 1/1, which equals 1 and 1/False is like 1/0 which results in an error.

The Lookup() function looks for the last entry that is less than or equal to the Lookup value... so looking for a 2 in an array of 1's and #DIV/0!'s will result in returning the last 1 in the array.... This is then matched to the Result Vector; C2:C3000 to pull the coinciding value..

[jgross]

i am 5 years to late
vlookup with a 3 as the last perameter instead of the normal false!

[ChemistB]

jg, that would not work. In fact, gives a value of 65 in the OP's example.

[jgross]

hmm perhaps the 3 in the last perameter only works with numbers as I am currently using it ina spreadsheet I am working on.
Sorry for the wrong information

[martindwilson]

vlookup with a 3 that is just the same as putting anything except 0
but for that to work you need to sort in ascending order