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

{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

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

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

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.