# VLOOKUP with duplicate returns

1. ## VLOOKUP with duplicate returns

I've created a vlookup to pull state and county when a zip code is entered.
Problem is that some zip codes are in multiple counties. for example: I live
in 54115, which is in Brown and Outagamie counties. the lookup only returns
Brown as it is listed first alphabetically (I assume). Any formula I can add
to let the user know of error or that multiple options/counties are
available? Thanks again

2. ## RE: VLOOKUP with duplicate returns

Hi, dandigger;
Two quick thoughts:

1) If all you want is an alert: Conditional formatting using a formula like:
=countif(\$A:\$A,\$C1)>1
Then format> patterns> yellow, all entered into the county results column.
Where
Column A: zip codes in your lookup table
Column C: zip codes entered to perform the lookup.

2) For a more complete solution: Multiple county columns in both your lookup
table and your results area, i.e.: county 1, county 2, as many as necessary.

Regards,
Ian.

"dandigger" wrote:

> I've created a vlookup to pull state and county when a zip code is entered.
> Problem is that some zip codes are in multiple counties. for example: I live
> in 54115, which is in Brown and Outagamie counties. the lookup only returns
> Brown as it is listed first alphabetically (I assume). Any formula I can add
> to let the user know of error or that multiple options/counties are
> available? Thanks again

3. ## Re: VLOOKUP with duplicate returns

Perhaps this set-up might provide some possibilities ..

Assume the reference table below is
In Sheet1, cols A and B, data from row2 down

Zip County
54115 Brown
54115 Outagamie
54116 County1
54116 County2
54117 County3
54117 County4
etc

Put in C1: =Sheet2!A1

Put in:
C2: =IF(A2="","",IF(A2=\$C\$1,ROW(),""))
D2: =IF(A2="","",IF(COUNTIF(\$A\$2:A2,A2)>1,"",ROW()))
E2:
=IF(ISERROR(SMALL(D:D,ROWS(\$A\$1:A1))),"",INDEX(A:A,MATCH(SMALL(D:D,ROWS(\$A\$1:A1)),D:D,0)))

Select C2:E2, copy down to say, E100, to cater for expected data in cols A
and B

In Sheet2
------
Put in A1: =LOOKUP(9.99999999999999E+307,Sheet3!A:A)

Put in A2:
=IF(ISERROR(SMALL(Sheet1!\$C\$2:\$C\$100,ROWS(\$A\$1:A1))),"",INDEX(Sheet1!B\$2:B\$11,MATCH(SMALL(Sheet1!\$C\$2:\$C\$100,ROWS(\$A\$1:A1)),Sheet1!\$C\$2:\$C\$100,0)))

Copy A2 down to A100
(same range size as in Sheet1)

In Sheet3 (Meant for data input)
------

Let's create 2 DV Lists, Zip and County

Click Insert > Name Define
Put in:
Names in workbook: Zip
Refers to:
=OFFSET(Sheet1!\$E\$2,,,SUMPRODUCT(--(Sheet1!\$E\$1:\$E\$100<>"")))
Click OK

Repeat steps above to create for: County
Names in workbook: County
Refers to:
=OFFSET(Sheet2!\$A\$2,,,SUMPRODUCT(--(Sheet2!\$A\$1:\$A\$100<>""))-1)

Now to apply the DVs onto cols A (Zip) and B (County)

Select col A
Click Data > Validation
Make the settings:
Under "Allow:" select List
Put in "Source:" box: = Zip
Click OK

Repeat for col B
Put in "Source:" box: = County
(Answer "Yes" to the warning prompt)

Select A1:B1,
Click Data > Validation > Ok (in dialog to erase & continue) > Ok

Put labels into A1:B1 : Zip, County
It's now ready for input

Select a zip in A2: 54115 (say)
The DV in the adjacent cell B2 will show the Counties applicable for 54115,
i.e.: Brown, Outagamie for selection

Continue with the zip input, select in A3: 54117 (say)
The DV in B3 will adjust to show the appilcable: County3, County4 for
selection
And so on ..

Inputs in cols A and B are assumed done progressively down from row2 without
skipping any blank rows. The DV in col B will reflect the zip selected in the
last (latest) entry in col A

--
Rgds
Max
xl 97
---
GMT+8, 1Â° 22' N 103Â° 45' E
xdemechanik <at>yahoo<dot>com
----

"dandigger" wrote:

> I've created a vlookup to pull state and county when a zip code is entered.
> Problem is that some zip codes are in multiple counties. for example: I live
> in 54115, which is in Brown and Outagamie counties. the lookup only returns
> Brown as it is listed first alphabetically (I assume). Any formula I can add
> to let the user know of error or that multiple options/counties are
> available? Thanks again

4. ## Re: VLOOKUP with duplicate returns

> The DV in col B will reflect the zip selected in the
> last (latest) entry in col A

Line above should read as:
> The DV in col B will reflect the applicable options
> for the zip selected in the last (latest) entry in col A

--
Rgds
Max
xl 97
---
GMT+8, 1Â° 22' N 103Â° 45' E
xdemechanik <at>yahoo<dot>com
----

5. ## Re: VLOOKUP with duplicate returns

> In Sheet2
....
> Put in A2:
>

=IF(ISERROR(SMALL(Sheet1!\$C\$2:\$C\$100,ROWS(\$A\$1:A1))),"",INDEX(Sheet1!B\$2:B\$1
1,MATCH(SMALL(Sheet1!\$C\$2:\$C\$100,ROWS(\$A\$1:A1)),Sheet1!\$C\$2:\$C\$100,0)))

Sorry, correction to formula above:

Put in A2:

=IF(ISERROR(SMALL(Sheet1!\$C\$2:\$C\$100,ROWS(\$A\$1:A1))),"",INDEX(Sheet1!B\$2:B\$1
00,MATCH(SMALL(Sheet1!\$C\$2:\$C\$100,ROWS(\$A\$1:A1)),Sheet1!\$C\$2:\$C\$100,0)))

("Sheet1!B\$2:B\$11" should read: "Sheet1!B\$2:B\$100")
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1