Vlookup issue

[jesoldano]

Hello—I am trying to write a simple vlookup. My formula is working as
designed but is only offering me a partial solution. What I would
like to do is to have the formula look a one list of mail codes, then
look at the other list of mail codes that correspond, and if there is
a description for that mail code, pull that description over. The
issue, however, is that more than one description can be tied to a
mail code. The way that I currently have my formula written, it will
only pull in the first description associated to that mail code, but
lops off all of the others, thus only returning partial results. Here
is my current formula:

=VLOOKUP(F:F, [Book1]Sheet1!$A:$B, 2, FALSE)

Where “A” = mailcode and B=description.

Is there any way around this?

 

[Dave Peterson]

=vlookup() returns the information from the first match it finds. It doesn't
bring back multiple values.

Maybe you can change the layout of that table.
Put the mailcode in column A and then the first description in column B, the
second in column C, the third in column D, ...

And use a few =vlookup()'s to bring back each of those descriptions.

Hello—I am trying to write a simple vlookup. My formula is working as
designed but is only offering me a partial solution. What I would
like to do is to have the formula look a one list of mail codes, then
look at the other list of mail codes that correspond, and if there is
a description for that mail code, pull that description over. The
issue, however, is that more than one description can be tied to a
mail code. The way that I currently have my formula written, it will
only pull in the first description associated to that mail code, but
lops off all of the others, thus only returning partial results. Here
is my current formula:

=VLOOKUP(F:F, [Book1]Sheet1!$A:$B, 2, FALSE)

Where “A” = mailcode and B=description.

Is there any way around this?

 

[jesoldano]

=vlookup() returns the information from the first match it finds.  Itdoesn't
bring back multiple values.

Maybe you can change the layout of that table.
Put the mailcode in column A and then the first description in column B, the
second in column C, the third in column D, ...

And use a few =vlookup()'s to bring back each of those descriptions.

Hello—I am trying to write a simple vlookup.  My formula is workingas
designed but is only offering me a partial solution.  What I would
like to do is to have the formula look a one list of mail codes, then
look at the other list of mail codes that correspond, and if there is
a description for that mail code, pull that description over.  The
issue, however, is that more than one description can be tied to a
mail code.  The way that I currently have my formula written, it will
only pull in the first description associated to that mail code, but
lops off all of the others, thus only returning partial results.  Here
is my current formula:

=VLOOKUP(F:F, [Book1]Sheet1!$A:$B, 2, FALSE)

Where “A” = mailcode and B=description.

Is there any way around this?

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you for the response. That is what I feared. I am unable to
manipulate the layout of the table, as it is a canned report that is
spit out of an online system, and we would like our users to as little
manual manipulation as possible. Do you know of any other formulas
that could help with this?

 

[Dave Peterson]

Check out Chip Pearson's site:
http://www.cpearson.com/Excel/TablesAndLookups.aspx

Look for "Arbitrary Lookups"

 

[Dave Peterson]

Another alternative is to use a User Defined Function.

How do you want to display those results? In one cell, but separated by
commas? In separate cells???

If you want a single cell:

http://groups.google.co.uk/group/mi...author:peterson&rnum=1&hl=en#db28f1ba868980a8

or

http://snipurl.com/24yfb (first post in the thread)


If you wanted separate cells (horizontal or veritical), take a look here:

http://groups.google.co.uk/group/mi...roup:*excel*+author:peterson#b5467b485b0a4f4d

or

http://snipurl.com/24yf3

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[myemail.an]

Where does the data reside? Is it possible to set up an ODBC
connection to the source of the data?
For example, you could think of setting up a very simple query in SQL
(you can write it in Microsoft Query, which returns the results
directly in Excel) to this end. Have you spoken to the IT department
of your organization?

 

[TomPl]

This is a long fomula but I think it will accomplish what you want:

=IF(ISNA(VLOOKUP(A2,G:H,2,FALSE)),,VLOOKUP(A2,G:H,2,FALSE)) &
IF(ISNA(VLOOKUP(A2,K:L,2,FALSE)),,VLOOKUP(A2,K:L,2,FALSE))