Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Excel N/A error message

### #1 ellac Posted 22 June 2005 - 09:03 AM

ellac

New Member

• Member
• 3 posts
Hi everyone

I am desperate for an answer. Here is an example of what I have:

Column E Column F
FOODSERVICE OTHER
#N/A #N/A
FOODSERVICE Check
#N/A #N/A
#N/A #N/A
MInt OTHER
#N/A #N/A
#N/A #N/A

I have a vlookup that looks up two other spreadsheets and brings in info. I get an #N/A which makes sense, but what I need it to do is for every title change I need the #N/A to be replaced with the title above it.

The problem is that it is a long list and titles change. I thought maybe there is some vba code that can help me.

Thanks everyone
• 0

### #2 dsm Posted 26 June 2005 - 05:43 AM

dsm

Member

• Member
• 98 posts
If the output you were expecting was as follows;

Column E Column F
FOODSERVICE OTHER
FOODSERVICE OTHER
FOODSERVICE Check
FOODSERVICE Check
FOODSERVICE Check
MInt OTHER
MInt OTHER
MInt OTHER

Then I have understood your question and VB may not be necesarry.

Here is a sample formula from cell B9
If the lookup value in cell A9 is not found (#N/A) then the ISERROR is true and the value from B8 will be returned instead.

=IF(ISERROR(VLOOKUP(A9,\$G\$1:\$H\$16,2,FALSE)),B8,VLOOKUP(A9,\$G\$1:\$H\$16,2,FALSE))

Hope this helps

Regards
David
• 0

### #3 ellac Posted 27 June 2005 - 08:08 AM

ellac

New Member

• Topic Starter
• Member
• 3 posts
Hi David

Thanks, it does work to a point but the problem is that in column E and F the titles change every few cells down so it will not always point to a specific cell.

Is there an addition to the vlookup or am I doing something wrong.

Thanks

Ella
• 0

### #4 dsm Posted 03 July 2005 - 06:47 AM

dsm

Member

• Member
• 98 posts
Ella,
My testing worked OK.

Try checking the formula that you copied down and check that the NA is pointing to the correct cells.

Check that you are using the full formula components that I provided.

If you are still having problems, attach a small sample of data with the formula intact and we can reveiw further.

Regards
David
• 0

### #5 ellac Posted 07 July 2005 - 07:50 AM

ellac

New Member

• Topic Starter
• Member
• 3 posts
Hi David

First of all thanks so much for taking the time to help me, I guess I am not very good at formulas and that is where my problem is.

Here is an example of the info, each time a title changes it should pick up the title not the N/A, this particular example starts on cell E6.

E F
6 CLUB COSTCO
7 0 0
8 #N/A #N/A
9 #N/A #N/A
10 #N/A #N/A
11 CLUB SAM'S
12 #N/A #N/A
13 CLUB SAM'S
14 #N/A #N/A
15 #N/A #N/A
16 #N/A #N/A
• 0

### #6 dsm Posted 09 July 2005 - 08:47 PM

dsm

Member

• Member
• 98 posts
Ella,
I am attaching an example spreadsheet.
It shows the results you require vs the the results you typically encounter.

I hope this helps you better understand what is happening.
Both blocks of data are looking for values in highligted block in the centre.

Regards
David

• 0

### #7 jasonharrod Posted 14 July 2005 - 02:47 PM

jasonharrod

Member

• Member
• 18 posts
The reason the #N/A show up if the data is in both spreadsheets is because the key value that you are entering isn't the same. If the spreadsheets look the same try doing a trim() function on the key column or select the column and do a replace find " " replace with nothing. this will trim out the white space in the key column.

Hope that helped.
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users