VLOOKUP with duplicate returns

[Guest]

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

 

[Guest]

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.

 

[Guest]

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,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(D,ROWS($A$1:A1)),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

 

[Guest]

The DV in col B will reflect the zip selected in the

last (latest) entry in col A

Line above should read as:

 

[Max]

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")