lookup problem - returning last value

[visitophir]

1

 

[Jonmo1]

1. Vlookup can't look from right to left (it's zoolander challenged). It can only find match in A and return from B. Can't find match in B and return from A. You would have to use an Index/Match formula instead.

2. vlookup will find the FIRST match, in your data set the 1st match is the EARLIER date, not the later date. Can your macro be adjusted to sort the data DESCENDING by column A, so that the Latest dates are on top, instead of on the bottom?

If you sort the data DESCENDING on the Date column, this will work

=INDEX(A:A,MATCH("STEWARTM",B:B,0))

 

[DiscoPistol]

In a new game I've invented called 'Guess the Question', here is the answer!

=INDEX(C10:C21,MATCH(G11,D10:D15,1))

DP

I should note that G11 is the name of the Person you are looking for. Be careful though as you have a leading space

 

[visitophir]

SORRY, deleted the question by mistake


Hi,
In column A I have logged in date and time (together) and in column B I have environ user ids
macros do the job of pasting this data one below the other whenever the spreadsheet is opened or is closed
cloumn A column B
<TABLE dir=ltr cellSpacing=0 cellPadding=2 width=176 border=0><TBODY><TR><TD width="62%">16-Jun-09 02 01


</TD><TD width="38%">TERRYS


</TD></TR><TR><TD width="62%">16-Jun-09 03 34


</TD><TD width="38%">STEWARTM


</TD></TR><TR><TD width="62%">16-Jun-09 03 54


</TD><TD width="38%">TERRYS


</TD></TR><TR><TD width="62%">16-Jun-09 03 58


</TD><TD width="38%">STEWARTM


</TD></TR><TR><TD width="62%">16-Jun-09 04 39


</TD><TD width="38%">CARROLLP


</TD></TR><TR><TD width="62%">16-Jun-09 04 39


</TD><TD width="38%">CARROLLP


</TD></TR></TBODY></TABLE>
This all is done in background on veryhidden sheet
On frontal page users get to see WELCOME - "USERID"
is it possible to show when they last logged in
for eg: if STEWARTM logged in latter, he would be shown that u had last looged in on 16th june 09 @ 03 34
I tried vlookup by reversing the cloumns , but everytime it returns the first value only.
Any help would be much appreciated
Thanks

 

[Jonmo1]

If the data cannot be sorted, this should find the LAST entry..

=LOOKUP(2,1/(B1:B1000="STEWARTM"),A1:A1000)

Note, that this formula cannot use entire column refs like A:A, must use specific row #s. Unless in xl2007. And both ranges must be same size.

 

[visitophir]

THANKS FOR REPLY

I tried to do the decending and then using the formula, however it returns the first value always

I know thats what i was look for in first place, however (apologies) whenever a person logs in it pastes his latest details and these then become the first or the last details entered so I would be looking for one less than that

Any thoughts on this

Thanks

Your help is much appreciated

 

[Jonmo1]

THANKS FOR REPLY

I tried to do the decending and then using the formula, however it returns the first value always

I know thats what i was look for in first place, however (apologies) whenever a person logs in it pastes his latest details and these then become the first or the last details entered so I would be looking for one less than that

Any thoughts on this

Thanks

Your help is much appreciated

That's why I asked if your macro can be adjusted to sort the data DESCENDING by the date. So that the latest dates are on top, therefor the FIRST match.

If your data cannot be sorted, and it just keeps adding records to the bottom of the list, go with my 2nd suggestion. That's probably easier anyway.

 

[visitophir]

Thanks JONMO

I added codes to sort it in descending fashion before adding environ and date/time (which gets added at the bottom) and it works absolutely fine

Thanks again

Ophir

 

[Jonmo1]

I would think you would want to sort AFTER adding the environ and date/time....
Otherwise, the latest environ and date/time entry are still on the bottom of the list...

 

[visitophir]

HI JONMO,

If I did it that way it gives me the very first logged in time for that user and not the previous one ( any user would have logged in around 20-30 times in a month).

Adding it at the bottom , exepmts it as I will be looking for the first value in the array with a simple vlookup

Your recommendation to sort it in decending order sorted the whole problem

Thanks very much

Ophir