"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.
--
Summer
snipped
|