Looking up first and last

[DRC]

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.

 

[new1]

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.  Iwould
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

 

[T. Valko]

Try this for the *last* instance:

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

 

[Ashish Mathur]

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. C512 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]

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.

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

 

[DRC]

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??

 

[DRC]

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

 

[T. Valko]

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