LOOKUP looking down

[Sandro]

I have a multi-user spreadsheet, which is continuously
updated by several users. In order versions can be
identified, a History worksheet is included in the file.
This worksheet contains columns being, Date, Comments &
Username. It is manually edited in consecutive date order
upon any user editing the spreadsheet.

The spreadsheet currently retrieves the date the file was
last altered from the History worksheet, for version
purposes. However I am wanting to record a summary of each
users last 'Comments' on the main spreadsheet by
retrieving the information from the History worksheet.

I have tried lookup function without success as it
apparently only works when the 'lookup vector'
('Username') is in sequential order. (Remembering the user
entry is not ordered.)

Is this possible as a formula for each specified username
without re-sorting the History list?

 

[Jason Morin]

Try VLOOKUP and specify an exact match by using 0 (zero)
in the fourth argument:

=VLOOKUP(---,---,---,0)

This overcomes the need to have the data sorted.

HTH
Jason
Atlanta, GA

 

[Sandro]

Jason,
Thanks for the quick response!
Tried Vlookup, and (oops) recognised had to re-sort the
History file beginning with Username in the 1st column.
When I used the function it returned the 1st entry
matching the Username, not the last.
ie. per the list below, Vlookup('John',A1:C4,2,0)
returned 'Comment2'...not Comment4.
That is, the value I require is the last entry.
Any further ideas?

eg. ref list
A B C
Fred Comment1 1/2/04
John Comment2 1/2/04
Sue Comment3 2/2/04
John Comment4 2/2/04

//Sandro
Adelaide,
South Australia

 

[Jason Morin]

I understand now. Forget VLOOKUP and try:

=INDEX(B1:B4,MAX(IF(A1:A4="John",ROW(A1:A4))))

Array-entered, meaning after inserting the formula, press
ctrl/shift/enter, not just enter. Excel will place {}
around the formula to indicate that it is an array formula.

G'Night!

HTH
Jason
Atlanta, GA