| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help for Novice User - Lookup Option maybe
I give; fought with this all day. I'm sure it's really easy for some of you
gurus that my jealousy turns green over. In one spreadsheet we constantly update addresses. The zip should identifiy the group the record belongs to without retyping every time. example zip code 77777 = Adam. I want the group name to display in a separate column. The data that indicates 77777 is assigned to Adam is in a separate worksheet. There could be multiple zips equal to Adam. There are 16 groups in all. Here's the way the worksheet with the group names looks: 77001 Abraham 77002 Adam 77003 Adam 77004 Adam 77005 Abraham 77006 Adam 77007 Joshua 77008 Joshua 77009 Moses 77010 Adam -- Thanks for everyone's help!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help for Novice User - Lookup Option maybe
Hi
Suppose your list with Group names are in Sheet3, try this formula: =VLOOKUP(77002,Sheet3!A1:B10,2) You can substiture the number to look up with a cell reference. Regards. Per On 9 Sep., 00:54, Gayle wrote: I give; fought with this all day. *I'm sure it's really easy for some of you gurus that my jealousy turns green over. In one spreadsheet we constantly update addresses. *The zip should identifiy the group the record belongs to without retyping every time. *example zip code 77777 = Adam. *I want the group name to display in a separate column. * The data that indicates 77777 is assigned to Adam is in a separate worksheet. *There could be multiple zips equal to Adam. *There are 16 groups in all. * Here's the way the worksheet with the group names looks: 77001 * Abraham 77002 * Adam 77003 * Adam 77004 * Adam 77005 * Abraham 77006 * Adam 77007 * Joshua 77008 * Joshua 77009 * Moses 77010 * Adam -- Thanks for everyone's help!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help for Novice User - Lookup Option maybe
Per, thank you!! This was a great start. It works fine when the zip code is
within the groups. However we have zips in Texas or even other states that do not appear in our groups but do appear in the member record. On those it needs to return NA or blank or something. Right now it's just picking one of the group. Any more ideas? -- Thanks for everyone''''s help!! "Per Jessen" wrote: Hi Suppose your list with Group names are in Sheet3, try this formula: =VLOOKUP(77002,Sheet3!A1:B10,2) You can substiture the number to look up with a cell reference. Regards. Per On 9 Sep., 00:54, Gayle wrote: I give; fought with this all day. I'm sure it's really easy for some of you gurus that my jealousy turns green over. In one spreadsheet we constantly update addresses. The zip should identifiy the group the record belongs to without retyping every time. example zip code 77777 = Adam. I want the group name to display in a separate column. The data that indicates 77777 is assigned to Adam is in a separate worksheet. There could be multiple zips equal to Adam. There are 16 groups in all. Here's the way the worksheet with the group names looks: 77001 Abraham 77002 Adam 77003 Adam 77004 Adam 77005 Abraham 77006 Adam 77007 Joshua 77008 Joshua 77009 Moses 77010 Adam -- Thanks for everyone's help!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help for Novice User - Lookup Option maybe
You need another agument to keep Excel from finding closest match.
=VLOOKUP(77002,Sheet3!A1:B10,2,FALSE) will return NA if not found. =IF(ISNA(VLOOKUP(77002,Sheet3!A1:B10,2,FALSE)),"", VLOOKUP(77002,Sheet3!A1:B10,2,FALSE)) will return a blank looking cell if not found. Gord Dibben MS Excel MVP On Wed, 9 Sep 2009 14:27:02 -0700, Gayle wrote: Per, thank you!! This was a great start. It works fine when the zip code is within the groups. However we have zips in Texas or even other states that do not appear in our groups but do appear in the member record. On those it needs to return NA or blank or something. Right now it's just picking one of the group. Any more ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Help for Novice User - Lookup Option maybe
Sorry, we've had internet problems so it took me a while to get back to this.
I don't know what happened I tried adding the False earlier and it didn't work; tried it today and it worked perfectly. Thanks so much!!! -- Thanks all!!! "Gord Dibben" wrote: You need another agument to keep Excel from finding closest match. =VLOOKUP(77002,Sheet3!A1:B10,2,FALSE) will return NA if not found. =IF(ISNA(VLOOKUP(77002,Sheet3!A1:B10,2,FALSE)),"", VLOOKUP(77002,Sheet3!A1:B10,2,FALSE)) will return a blank looking cell if not found. Gord Dibben MS Excel MVP On Wed, 9 Sep 2009 14:27:02 -0700, Gayle wrote: Per, thank you!! This was a great start. It works fine when the zip code is within the groups. However we have zips in Texas or even other states that do not appear in our groups but do appear in the member record. On those it needs to return NA or blank or something. Right now it's just picking one of the group. Any more ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Novice user trying to use variables including text and numbers | Excel Worksheet Functions | |||
| novice excel 2007 user needs help | New Users to Excel | |||
| Several things if anyone is willing to help a total novice user ofExcel | Excel Discussion (Misc queries) | |||
| Hiding Worksheets and Unhiding them easily for Novice User | Excel Discussion (Misc queries) | |||
| Very Novice Excel user with security question | Excel Discussion (Misc queries) | |||
Linear Mode
