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?

=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.

wrote:

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

On Aug 8, 11:27*am, Dave Peterson wrote:
=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.





wrote:

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- 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?

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

Look for "Arbitrary Lookups"

wrote:

<<snipped
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

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/mic...28f1ba868980a8

or

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


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

http://groups.google.co.uk/group/mic...467b485b0a4f4d

or

http://snipurl.com/24yf3

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
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.)

Dave Peterson wrote:

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

Look for "Arbitrary Lookups"

wrote:

<<snipped
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

--

Dave Peterson

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?

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?- Hide quoted text -

- Show quoted text -

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

" wrote:

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?