Looking up first and last
 

I have city names and postal codes. There are several postal codes for each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes are
usually in sequencial order. Using Vlookup I can get the first instance of
the postal code but I can't figure out how to get the last instance. I would
then be able to say that High River has a postal code range from X to XX.
Can anyone help.

Looking up first and last
 

On 15 sep, 22:49, DRC wrote:
I have city names and postal codes. There are several postal codes for each
city.
Cities are in colum V and Postal codes are in column W. *Example:
there are 210 different postal codes for High River. these postal codes are
usually in sequencial order. *Using Vlookup I can get the first instance of
the postal code but I can't figure out how to get the last instance. *I would
then be able to say that High River has a postal code range from X to XX. *
Can anyone help.

Hello,

Just an idea, if postal codes are in a sequencial order...And if
you're postal codes are all in numeric format, maybe you could try
making a pivot table with city names in column and min and max postal
codes as data.

Hope it helps.

new1

Looking up first and last
 

Try this for the *last* instance:

=LOOKUP(2,1/(V1:V100="High River"),W1:W100)

--
Biff
Microsoft Excel MVP


"DRC" wrote in message
...
I have city names and postal codes. There are several postal codes for each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes
are
usually in sequencial order. Using Vlookup I can get the first instance
of
the postal code but I can't figure out how to get the last instance. I
would
then be able to say that High River has a postal code range from X to XX.
Can anyone help.

Looking up first and last
 

Hi,

You can use this array formula (Ctrl+Shift+Enter) for the last occurrence

=INDEX($C$5:$D$12,SMALL(IF($C$5:$C$12=$C$14,ROW($C $5:$C$12)-ROW($D$4)),COUNTIF($C$5:$C$12,C14)),2)

C14 holds the city. C5:D12 has the city in column C and postal codes in
column D. Row 5 has the headings.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"DRC" wrote in message
...
I have city names and postal codes. There are several postal codes for
each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes
are
usually in sequencial order. Using Vlookup I can get the first instance
of
the postal code but I can't figure out how to get the last instance. I
would
then be able to say that High River has a postal code range from X to XX.
Can anyone help.

Looking up first and last
 

In Canada we use a Alph numerical postal code system, I tried the Pivot table
min/max and all it returned was zero's . Thanks anyways.

" wrote:

On 15 sep, 22:49, DRC wrote:
I have city names and postal codes. There are several postal codes for each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes are
usually in sequencial order. Using Vlookup I can get the first instance of
the postal code but I can't figure out how to get the last instance. I would
then be able to say that High River has a postal code range from X to XX.
Can anyone help.

Hello,

Just an idea, if postal codes are in a sequencial order...And if
you're postal codes are all in numeric format, maybe you could try
making a pivot table with city names in column and min and max postal
codes as data.

Hope it helps.

new1

Looking up first and last
 

Thanks. This worked for the last instance. Great. I have done a lot with
lookup's and vlookups. I have never used the array lookup.
I don't understand the lookup 2 and then what are you dividing by 1??

"T. Valko" wrote:

Try this for the *last* instance:

=LOOKUP(2,1/(V1:V100="High River"),W1:W100)

--
Biff
Microsoft Excel MVP


"DRC" wrote in message
...
I have city names and postal codes. There are several postal codes for each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes
are
usually in sequencial order. Using Vlookup I can get the first instance
of
the postal code but I can't figure out how to get the last instance. I
would
then be able to say that High River has a postal code range from X to XX.
Can anyone help.

Looking up first and last
 

Thanks for the respones, I haven't tried this yet but plan to.

"Ashish Mathur" wrote:

Hi,

You can use this array formula (Ctrl+Shift+Enter) for the last occurrence

=INDEX($C$5:$D$12,SMALL(IF($C$5:$C$12=$C$14,ROW($C $5:$C$12)-ROW($D$4)),COUNTIF($C$5:$C$12,C14)),2)

C14 holds the city. C5:D12 has the city in column C and postal codes in
column D. Row 5 has the headings.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"DRC" wrote in message
...
I have city names and postal codes. There are several postal codes for
each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes
are
usually in sequencial order. Using Vlookup I can get the first instance
of
the postal code but I can't figure out how to get the last instance. I
would
then be able to say that High River has a postal code range from X to XX.
Can anyone help.

Looking up first and last
 

Let's use this example to demonstrate how this works:

...........A.............B
1.....header.....header
2........HR...........10
3........xx.............12
4........HR...........15
5........aa.............16

Return the value in column B that corresponds to the *last instance* of HR
in
column A.

=LOOKUP(2,1/(A2:A5="HR"),B2:B5)

This expression will return an array of either TRUE or FALSE:

(A2:A5=HR)

A2 = HR = HR = TRUE
A3 = xx = HR = FALSE
A4 = HR = HR = TRUE
A5 = aa = HR = FALSE

We then use the divison operation to coerce those logical values to numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!),B2:B5)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector that is less than the lookup_value.

The *last* value in the lookup_vector that is less than the lookup_value is
the second 1. The #DIV/0! errors are ignored. We use a lookup_value of 2
because we know that the result of this expression:

1/(A2:A5=HR)

will not return a value greater than 1.

This is how that would look:

...........A.............B
1.....header.....header
2........1.............10
3...#DIV/0!.......12
4........1.............15
5...#DIV/0!.......16

So, the *last instance* of HR was in A4. Return the corresponding value from
B4.

=LOOKUP(2,1/(A2:A5=HR),B2:B5) = 15



exp101
--
Biff
Microsoft Excel MVP


"DRC" wrote in message
...
Thanks. This worked for the last instance. Great. I have done a lot with
lookup's and vlookups. I have never used the array lookup.
I don't understand the lookup 2 and then what are you dividing by 1??

"T. Valko" wrote:

Try this for the *last* instance:

=LOOKUP(2,1/(V1:V100="High River"),W1:W100)

--
Biff
Microsoft Excel MVP


"DRC" wrote in message
...
I have city names and postal codes. There are several postal codes for
each
city.
Cities are in colum V and Postal codes are in column W. Example:
there are 210 different postal codes for High River. these postal codes
are
usually in sequencial order. Using Vlookup I can get the first
instance
of
the postal code but I can't figure out how to get the last instance. I
would
then be able to say that High River has a postal code range from X to
XX.
Can anyone help.