Problem with 2 VLOOKUP s and ISNA

**Jogier505** · 06-10-2010, 06:36 PM

The formula works fine, but I am trying to add an [ISNA] to the formula with no success.

=VLOOKUP($C$5,Data,3,FALSE)&", "&VLOOKUP($C$5,Data,4,FALSE)

I can get either part to work but I am having no luck when I try to combine the two lookups

=IF(ISNA(VLOOKUP($C$5,Data,3,FALSE)),""&", "&IF(ISNA(VLOOKUP($C$5,Data,4,FALSE)),"",IF(ISNA(VLOOKUP($C$5,Data,3,FALSE)),"",&", "&IF(ISNA(VLOOKUP($C$5,Data,4,FALSE))

It should return a City from Data3 and a State from Data4 with a coma separation and show no value if their is no value in the cell being queried. such as in the formula below.

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

Thanks

Jim O

**daddylonglegs** · 06-10-2010, 07:04 PM

If you just want no value if C5 is blank try

=IF($C5="","",VLOOKUP($C$5,Data,3,FALSE)&", "&VLOOKUP($C$5,Data,4,FALSE))

**Jogier505** · 06-10-2010, 07:45 PM

DaddyLL,

Cell C5 will always have a name. It is the refference for the "Data" table. My problem is that not all the rows in "Data3 or 4" contain text (either a City or a State). When that cell is blank I wish to return a blank value. I can get it to work with only one Lookup such as,

=IF(ISNA(VLOOKUP($C$5,Data,2,FALSE)),"",VLOOKUP($C$5,Data,2,FALSE))

Its when I try to combine the 2 Lookups and add the ISNA function that I run into a problem. I would like the result to be "City, State" or if their is no listed City and State to return a blank. The formula below works if I am looking up only one column.

=IF(ISNA(VLOOKUP($C$5,Data,2,FALSE)),"",VLOOKUP($C$5,Data,2,FALSE))

JIm O

**daddylonglegs** · 06-10-2010, 07:54 PM

......but you only get #N/A if C5 doesn't find a match in the first column of data, if it matches but finds a blank in the return column that isn't an error so you won't eliminate it by using ISNA

If the first VLOOKUP returns a blank and the second one doesn't (or vice versa) what do you want to do then?

Is it possible that C5 won't be in the first column of data?

**Jogier505** · 06-10-2010, 08:58 PM

DaddyLL,

I am putting together a Phone List with addresses. "Data" referes to the table where column "A" is the list of names, column "B" is the address, column "C" is the city and column "D" is the state. The only column completely full is "A", the rest may have blank cells. Cell "C5" is a dropdown list with the "data" table on another sheet.

Using the ISNA function allowes me to return a blank value in such a case. But as with the other columns I am looking up only one value. Using &","& with Vlookup I am able to display the City and state as "Columbus, Ohio". If their is no value in the cell being searched I now get a N/A. I would like to be able to eliminate that and display a blank or City, State. If need be and the first column is blank a blank value would be fine.

I hope this clears it up.

Jim O