Home |
Search |
Today's Posts |
#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
|
|||
|
|||
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
|
|||
|
|||
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 <atyahoo<dotcom ---- "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
|
|||
|
|||
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 <atyahoo<dotcom ---- |
#5
|
|||
|
|||
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)),Sh eet1!$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)),S heet1!$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 <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
When VLOOKUP returns a #N/A How can you get it to replace #N/A wi. | Excel Worksheet Functions | |||
Vlookup returns incorrect match | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
vlookup returns n/a | Excel Worksheet Functions |