Need help to finish function....
 

Here is my function....
=IF(ISNA(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5&6&7,FALSE))

I am trying to join text on a row in the 5th, 6th and 7th colums of
workbook into 1 cell in another workbook, thus my guess of 5&6&7, which was
a bad guess.

Column 5 is the city, 6 is the state, and 7 is the zip code. I would like
to pull it into one cell in the following format: city, state zip.

What to I need to replace the 5&6&7 with to make this work?

Thanks,
Dan

Need help to finish function....
 

{5,6,7}

Alan Beban

Dan B wrote:
Here is my function....
=IF(ISNA(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5&6&7,FALSE))

I am trying to join text on a row in the 5th, 6th and 7th colums of
workbook into 1 cell in another workbook, thus my guess of 5&6&7, which was
a bad guess.

Column 5 is the city, 6 is the state, and 7 is the zip code. I would like
to pull it into one cell in the following format: city, state zip.

What to I need to replace the 5&6&7 with to make this work?

Thanks,
Dan

Need help to finish function....
 

The formula I posted will return the values to 3 different cells; I'll
take another stab at returning them to one cell, if no one else does first.

Alan Beban

Dan B wrote:
Here is my function....
=IF(ISNA(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5&6&7,FALSE))

I am trying to join text on a row in the 5th, 6th and 7th colums of
workbook into 1 cell in another workbook, thus my guess of 5&6&7, which was
a bad guess.

Column 5 is the city, 6 is the state, and 7 is the zip code. I would like
to pull it into one cell in the following format: city, state zip.

What to I need to replace the 5&6&7 with to make this work?

Thanks,
Dan

Need help to finish function....
 

"Dan B" wrote...
Here is my function....
=IF(ISNA(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5&6&7,FALSE))

I am trying to join text on a row in the 5th, 6th and 7th colums of
workbook into 1 cell in another workbook, thus my guess of 5&6&7, which was
a bad guess.

Column 5 is the city, 6 is the state, and 7 is the zip code. I would like
to pull it into one cell in the following format: city, state zip.

I think you want

=IF(ISNUMBER(MATCH($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$A$710,0)),
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$710,5,0)&", "&
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$710,6,0)&" "&
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$710,7,0),"")

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

Need help to finish function....
 

Awesome!! That worked great!!

Thanks!



I think you want

=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$710,5,0)&", "&
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$710,6,0)&" "&
VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$710,7,0),"")

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.