JMB and all,

Sorry, forgot to add the final solution to my last post (so others who are
learning will not get confused - I hope).

=IF(A11="","",VLOOKUP(A11,ClientAddress,3,FALSE)&" ,"&"
"&VLOOKUP(A11,ClientAddress,4,FALSE)&" "&VLOOKUP(A11,ClientAddress,5,FALSE))

Where 3 denotes the column number on sheet2 (Clients) for City; 4 denotes
the column number for State; and 5 denotes the column number for Zip.

I removed the extra named ranges I had (ahem) created previously since they
aren't necessary: City, State Zip.

I seem to know just enough to be dangerous, huh?
--
Summer (no valid email)

"JMB" wrote in message
...
| My apologies.
|
| I just shorthanded VLookup(City).
|
| VLOOKUP(A11,ClientAddress,3,FALSE) I believe is the function in your
| previous post that looks up the City. Since it appeared you were already
| using VLookup correctly (with 4 arguments) I didn't retype the entire
| function.
|
|
|
|
| "Summer" wrote:
|
| Hi Myrna,
|
| Thanks for responding. To whom are you making this suggestion?
| ::
| --
| Summer
|
| "Myrna Larson" wrote in message
| ...
| | If you check Help for VLOOKUP, you'll see that it requires at least 3
and
| | possibly 4 arguments. You've supplied only 1.
| |
| | I suggest you learn to use this function, as it's very useful.
| |
| |
| | On Mon, 13 Jun 2005 15:55:19 GMT, "Summer"

| | wrote:
| |
| | "JMB" wrote in message
| | ...
| | |I think the problem is I left out the & right before VLOOKUP(State).
| |
| | Hi,
| |
| | I still could not get this one to work with your suggested revision.
When
| I
| | added the ampersand, the formula still returned "You've entered too
few
| | arguments for this function.". (I also added the equals sign and the
| | specified space after the specified comma):
| |
| | Your formula as originally posted:
| |
| | IF(A11="","",VLOOKUP(City)&"," VLOOKUP(State)&" "&VLOOKUP(Zip))
| |
| | Your formula with our revisions:
| |
| | =IF(A11="","",VLOOKUP(City)&","&" "&VLOOKUP(State)&" "&VLOOKUP(Zip))
| |
| | Does not work. Sorry.
| | ----------------------
| | My understanding is that VLOOKUP requires four arguments. For
example:
| |
| | lookup_value______
| table_array__________col_index_num_________range_l ookup
| |
| | A11_____________ClientAddress________3___________ ________FALSE (to
| specify
| | exact match)
| |
| | The arguments in the whole formula would read something like: IF A11
is
| | blank, then leave blank, otherwise IF A11 = company name, THEN lookup
| named
| | range of ClientAddress and return value in column 3, must be exact
match.
| | ----------------
| | I WAS able to get your formula to work if I gave City, State and Zip
EACH
| | the same range as ClientAddress and included all four arguments for
each
| | VLOOKUP function.
| |
| | The original named ranges we
| |
| | ClientAddress- refers to: =Clients!$B$3:$F$25
| | Clients- refers to: =Clients!$B$3:$B$25
| | Services- refers to: =Services!$A$2:$A$7 (ignore - not appl. here)
| |
| | Added named ranges a
| |
| | City- refers to: =Clients!$B$3:$F$25
| | State- refers to: =Clients!$B$3:$F$25
| | Zip-refers to: =Clients!$B$3:$F$25
| |
| |
| | There are no headers included in the ranges. I wonder if that would
that
| | make a difference. I'll have to check this out.
| |
| | The final result with all revisions:
| |
| | =IF(A11="","",VLOOKUP(A11,City,3,FALSE)&","&" "
| | &VLOOKUP(A11,State,4,FALSE)&" "
| | &VLOOKUP(A11,Zip,5,FALSE))
| |
| | (This works! 106 characters long compared to the first revision of
133
| char.
| | long compared to the original of 169 char. long )
| |
| | Thanks for all your helpful suggestions! I learned a lot from our
| exchange.
| |
|
|
|
|