A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

find LAST match in column



 
 
Thread Tools Display Modes
  #1  
Old February 24th, 2005, 10:43 AM
Adam
external usenet poster
 
Posts: n/a
Default find LAST match in column

Hi Domenic,

I just saw this correspondence and I wonder if you could explain the formula
because i do not understand how it can return the right value (even though it
does!)
For instance, how do the formula know that the input 1/A1:A1000"" refers
to the last cell?

Thanks
Adam

"Domenic" skrev:

Yes, definitely! Thanks for the reminder, Harlan!

Although, wouldn't 2 serve better as the lookup value?

For example, take the following table...

Bob a
Jack b
Jane c
Bob d
Jill e
Phil f
Bob g
Jason h

If you use...

=LOOKUP(1,1/(A1:A100="Bob"),B1:B100)

...the correct result is returned, that being "g".

However, if you use...

=LOOKUP(1,1/(A1:A8="Bob"),B1:B8)

...an incorrect result is returned, that being "d".

Using 2 as the lookup value returns the correct result regardless of
the range one uses.

  #2  
Old February 24th, 2005, 12:18 PM
Aladin Akyurek
external usenet poster
 
Posts: n/a
Default

The formula extends a formula for finding the (position of) last numeric
value in a range. The extension is due to Harlan Grove. How it works is
explained he

http://tinyurl.com/7ysq5

Adam wrote:
Hi Domenic,

I just saw this correspondence and I wonder if you could explain the formula
because i do not understand how it can return the right value (even though it
does!)
For instance, how do the formula know that the input 1/A1:A1000"" refers
to the last cell?

Thanks
Adam

"Domenic" skrev:

Yes, definitely! Thanks for the reminder, Harlan!

Although, wouldn't 2 serve better as the lookup value?

For example, take the following table...

Bob a
Jack b
Jane c
Bob d
Jill e
Phil f
Bob g
Jason h

If you use...

=LOOKUP(1,1/(A1:A100="Bob"),B1:B100)

...the correct result is returned, that being "g".

However, if you use...

=LOOKUP(1,1/(A1:A8="Bob"),B1:B8)

...an incorrect result is returned, that being "d".

Using 2 as the lookup value returns the correct result regardless of
the range one uses.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
unable to repair inobox Sudheer Mumbai General Discussion 1 February 20th, 2005 12:55 PM
need to find which numbers (3+) in a column sum to a value Devin General Discussion 1 February 11th, 2005 11:30 PM
How to sort/update large excel db ConfusedNovice General Discussion 15 February 2nd, 2005 01:43 AM
Find value in column and associate result to it Eduardo General Discussion 1 June 17th, 2004 03:12 PM
Find highest value in column Don Guillett Worksheet Functions 1 May 5th, 2004 01:37 PM


All times are GMT +1. The time now is 01:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.