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