Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Vlookup to return blank cell instead of n a Results

HI, is there anyway to have a vlookup formula display a blank cell instead of #N/A if the value it was looking for is not found. For example the first spreadsheet contains the persons name say Bob, the 2nd spreadsheet that the vlookup formula is searching does not contain the name Bob right now excel shows #N/A but what I want it to show is a blank cell. I've tried conditional formating to just white out the text if #N/A appears but can't seem to get that to work...any ideas?

This is similar to other questions I've seen posted but unfortunately I can't
get my formula to work. If the value I lookup does not have a value on the
pivot table I'm searching, I'd like to return a blank cell instead of #N/A in
the cell. The vlookup formula I'm using is: =VLOOKUP($B2,'Vacay
Pivot'!$A$5:$G$1788,2,FALSE) I'm by no means great in excel so if anyone can
offer advice as to how to use an if statement with this lookup formula to
return a blank cell instead of #N/A I would be SOOO grateful.
Thank you!

The Data sheet contains a column with numeric values. The lookup table deliberately does not contain all of the values found in the Data sheet. When the formula
=VLOOKUP(I6,Fire,2,FALSE)
is applied the result is #N/A when the value in I6 is not found in "Fire" lookup.
Is there a way to have the result display as a blank cell instead of #N/A when this condition occurrs.

Thanks in anticipation.

I want to pull info from my master over to other sheets, if nothing is in the cell I want it to return a blank cell instead of zero so this is the formula I am using. The only problem is my formulas start row 12, rows 15-28 either won't pull info over or if blank it puts in a zero. 29-560 works fine then in row 561-596 nothing, zeros, or #NA.

=IF(LEN(VLOOKUP($A12,'FD RUN LIST - MASTER'!$A$12:$N$596,$B$17))=0,"",(VLOOKUP($A12,'FD RUN LIST - MASTER'!$A$12:$N$596,$B$17,FALSE)))

Is there another formula I should use so I get a blank instead of zero?

Wow! That "Vlookup" works awesome! I'll need to study that more to find out exactly why and how it works to utilize it in the future.

Okay - now next question - how do I get it to report a blank cell instead of "#N/A" so that I can copy my formula down??

Getting the blank cell with a formula in it seems to be my biggest struggle!!

I imagine the reason I'm getting the #N/A error is due to the fact that one column does not have any data - yet.

The main sheet has a column to enter an amount - from 10 to 999999999. The next column then does the "Lookup" to see what number was entered in the 1st column and give it the proper percentage.

SO - the 1st column won't have any data until someone put the data in. BUT - if I copy my "Lookup" formula all the way down (and then protect the worksheet) this is where the #N/A error comes in because it's looking for data in that first column.

I was given this formula: =if(isna(vlookup expression),"",vlookup expression)

But to be honeset I am not sure what the (vlookup expression) means. I first thought it was my original function of Vlookup - but if that's the case - would I need to enter it twice??

Thanks - confused - but hopeful!

Oz

What looks wrong here?

=if(isna(VLOOKUP(B11, table!$A$2:$B$10,2,TRUE)," ",VLOOKUP(B11, table!$A$2:$B$10,2,TRUE))

Something's not right here as when I type it this way - it tells me there are errors with it and then highlights the quotation marks.

Side note: I'm attempting to return a blank cell instead of #N/A. The B11 column will have data entered as needed - but I want to copy the formula on down without the #N/A coming up.

Os

Hi, I am new to using the VLOOKUP function and would like a bit of help.

I have created a sheet labelled 'Lookup List' and have data in the range
B4:H52 and sorted by column B

as follows.
B C D E F
G H
Species Date Place lbs ozs drms
Kilos
Barbel 25-Aug-1984 River Kennet 2 13 0.0 1.275
Chub 01-Jan-1980 Luxborough Pit 1 5 0.0 0.595
Chub 01-Mar-1981 R. Roding, Chigwell 1 4 0.0 0.566
Chub 01-Mar-1981 R. Roding, Chigwell 1 3 0.0 0.538
Chub 10-Sep-1983 R. Lea, 0 15 0.0
0.425
Bream 01-Jun-1981 Lower Lough Erne 2 12 0.0 1.247
Bream 01-Jul-1982 River Beult, Hunton 1 12 0.0 0.793

I have used the following formula to populate a list on a different sheet.

=VLOOKUP("CHUB",'Lookup List'!B4:H52,2,TRUE) amending the Col index number
for each column.

The result was as follows producing 5 entries instead of the expected 4 and
all 5 entries are the details of the last entry for Chub in the lookup list.

Date Place lbs ozs drms
Kilos
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
10-Sep-1983 R. Lea, 0 15 00.0 0.425
#N/A #N/A #N/A #N/A #N/A #N/A

I have obviously got the formula wrong or misinterpreted the way this
function works. Is there any way to get this to work? If so could you also
tell me how I can return a blank cell instead of #N/A when the result is not
TRUE?

Any help would be gratefully appreciated.

--
John
Delete extra @ to reply

I am using an @vlookup to return a "Y" or an "X" value back. When I try to
change this function, instead of getting a "Y", "X" or blank cell, the
formula is what shows in my cell vs the text I want returned. When I
originally set up the fx in this spreadsheet, it was fine. It wasn't until I
needed to edit each month that I have this problem. Even it I delete the fx
and re enter, I still get the formula returnevs the value. See Below what
comes back in my cell. The Y's are correct. Not sure why the formula is
returned vs a Y, N or blank cell. Not sure if there is something I need to
turn off or on. I looked in the tools - options and did not see anything.

=VLOOKUP($B15,'Station Rep Guide'!$C$3:$k$1089,8,FALSE)
Y
Y
Y

Currently I have the formula below. When its true, it retuns what I need. When its False (or does not find any matching) it returns #N/A. I want it to be blank cell instead of showing the #N/A. How can i fix that?

=IF(A6="","",VLOOKUP(A6,'Table for Talk Ref'!$E$4:$F$236,2,0))

Thanks... Stumped

Greetings all,

I have a weekly table of part numbers and inventory quantities I'm tracking
for a certain area.
I also get a table showing the net changes from the previous week in this
same area.
Not every PN in my inventory list will have changed that week.
My lookup table contains only those PNs that have changed in the last week
so it is only a subset of the main inventory list.

I created a VLOOKUP formula for every row of my inventory list that will
look in the change table for any changes and return that value to the cell.

=VLOOKUP(B2,$H$4:$I$18,2,FALSE)

Right now when the lookup value (PN) doesn't exist in the table the cell
returns "#N/A".
I want to qualify my formula so that if the exact lookup value (PN) is not
in the lookup table, it will return a "blank" cell.
I tried using an IF statement such as:

=IF(VLOOKUP(B2,$H$4:$I$18,2,FALSE)="N/A","",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

....but obviously the text "N/A" is not the value of the cell for conditional
testing. I found that the ERROR.TYPE() for "N/A" = 7,
so then I tried...

=IF(ERROR.TYPE(VLOOKUP(B2,$H$4:$I$18,2,FALSE))=7," ",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

and this gives me the blank cell I wanted, but for those rows where there IS
a value in the lookup table it now returns "#N/A" instead of the value.

What do I need to do?

Thanks for your help,
Scott

Greetings all,

I have a weekly table of part numbers and inventory quantities I'm tracking
for a certain area.
I also get a table showing the net changes from the previous week in this
same area.
Not every PN in my inventory list will have changed that week.
My lookup table contains only those PNs that have changed in the last week
so it is only a subset of the main inventory list.

I created a VLOOKUP formula for every row of my inventory list that will
look in the change table for any changes and return that value to the cell.

=VLOOKUP(B2,$H$4:$I$18,2,FALSE)

Right now when the lookup value (PN) doesn't exist in the table the cell
returns "#N/A".
I want to qualify my formula so that if the exact lookup value (PN) is not
in the lookup table, it will return a "blank" cell.
I tried using an IF statement such as:

=IF(VLOOKUP(B2,$H$4:$I$18,2,FALSE)="N/A","",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

....but obviously the text "N/A" is not the value of the cell for conditional
testing. I found that the ERROR.TYPE() for "N/A" = 7,
so then I tried...

=IF(ERROR.TYPE(VLOOKUP(B2,$H$4:$I$18,2,FALSE))=7,"",VLOOKUP(B2,$H$4:$I$18,2,FALSE))

and this gives me the blank cell I wanted, but for those rows where there IS
a value in the lookup table it now returns "#N/A" instead of the value.

What do I need to do?

Thanks for your help,
Scott

I've googled this and i'm stumped. In short, I have a worksheet to display information from another worksheet using a drop menu. The following function is returning a 0 (or blank after tools->view zero cell blank checked), instead of the TRUE argument.

=IF(ISNA(VLOOKUP(B302,ACCOUNTS!$A$2:$K$300,4,FALSE)),"Not Found",(VLOOKUP(B302,ACCOUNTS!A2:K300,4,FALSE)))

The cell in question is blank, nothing in there. However, if I type something then that will appear.

Why can't I get it to say "N/A" instead of returning a blank, but it WILL show if something is there?

Thanks for any and all help! Let me know if clarification is needed.

Hi, when I use a vlookup formula instead of displaying N/A if it cant find anything I would like it to just be a blank cell. Please can someone help.

Hello,

Sorry for bugging you guys but I have a quick problem. I have a list of words which consists of words. I want to search for a particular word from this list and when the word isnt found, I want an output of an empty space in the cell (" "). Below is what I have

A B
1
2 yes_1 Hello
3 yes_2 Goodbye
4 xxx Hi
5 xxx Try

My code at cell A9 is:

=VLOOKUP("yes_3",$A$36:$G$53,7,FALSE)

Instead of an output of #N/A....how do i get the cell to show a blank cell?

Once again thank you for your time.

I have created a spreadsheet using vlookups. If a cell is blank, the other
cell returns #n/a. However, a total may be in another cell. I am adding two
sets of information in another cell. If one cell has a number and other has
n/a, it returns n/a instead of the total number. The help screen for n/a
says use brackets (), but it does not make any sense. SUMIF, IF, COUNTIF
has not worked to get the End Total to add when there is a #n/a in the
sub-total fields.

Types Cost Amount Total Types Cost Total End
Total
Metro 2.50 1 2.50 n/a
2.50 **

** It returns #n/a instead of $2.50. What is the correct formula to by
pass the cell that shows n/a instead of a number?

A large proportion of questions in Excel newsgroups and Q&A boards regard #N/A (the Not Available error) the look up worksheet functions return.

The quotes that follow are from the Help file (of Excel 2000).

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

LOOKUP(lookup_value,lookup_vector,result_vector)

Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

LOOKUP(lookup_value,array)

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Remarks

If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.

If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

MATCH(lookup_value,lookup_array,match_type)

Remarks

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

MATCH does not distinguish between uppercase and lowercase letters when matching text values.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Although kindred, I excluded INDEX and CHOOSE, because they don't return #N/A.

What follows is a quote from a reply of mine at microsoft.public.excel.worksheet.functions to a post entitled "Conditional formatting to hide #N/A results", along with a reply to the same post by Harlan Grove:

It involves a proposal to extend the syntax of the look up functions quoted above with an (extra) optional argument.

QUOTE

A richer syntax for lookup functions would allow us to escape testing what
these functions compute (return), so avoiding the "compute twice" trap we
often see.

My proposal is simple: Add an optional slot to the sysntax of these
functions.

For VLOOKUP this surgery would give:

=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})

{} means optional; the default value for the 5th argument should be #N/A (in
view of backward compatibilty).

Examples:

=VLOOKUP(x,Table,c,0) [ return #N/A by default upon failure ]

=VLOOKUP(x,Table,c,0,"") [ return blank upon failure ]

=VLOOKUP(x,Table,c,0,0) [ return 0 upon failure ]

=VLOOKUP(c,Table,c,1) [ return #N/A by default upon failure; although in
most [cases] avoidable by approriate structuring of Table ]

=VLOOKUP(c,Table,c,1,0) [ return 0 upon failure; although in most [cases]
avoidable by approriate structuring of Table ]

PS. This is a renewal of a thread I was involved with at
http://www.mrexcel.com/archive2/messages/13513.html

What do you think? Am I overlooking something that would stand such a change
in the way?

Aladin

"Harlan Grove" wrote in message
news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...
> Peo Sjoblom wrote...
> >One possible way would be to use a white font and the formula
> >
> >=iserror(a1)
> >
> >assuming you want to hide it in A1
> >
> >You can of course trap the error using if and isna in the formula as
well..
> >
> >=if(isna(formula),"",formula)
>
> But it involves evaluating formula twice, which can seriously slow down
> Excel when formula is long and/or complicated. There are times when VBA
> user-defined functions can actually speed up Excel.
>
> 'trap errvals and return specified value or "" instead - return v if
> 'it's not a marched errval
> 'args: v is the *scalar* value to check
> ' e is an optional list of additional args used as follows
> ' - if 1st arg after v isn't an errval, use it as the return value
> if v is a
> ' matched errval; otherwise, use "" as the return value
> ' - all remaining args are treated as errvals to match v against,
so
> if
> ' no remaining args, match all errvals
> 'note: nonerrval args after 2nd arg effectively ignored
> '
> Function errortrap(v As Variant, ParamArray e() As Variant) As Variant
> Dim i As Long, m As Long, n As Long, t As Variant
>
> errortrap = v
>
> If Not IsError(v) Then Exit Function 'return quickly when not errval
>
> n = UBound(e)
>
> If Not IsError(e(0)) Then
> m = 1
> t = e(0)
> Else
> m = 0
> t = ""
> End If
>
> If n < m Then 'no more args, so matches all errvals
> errortrap = t
> Exit Function
> End If
>
> For i = m To n 'check specified errvals
> If v = e(i) Then
> errortrap = t
> Exit Function 'can return now
> End If
> Next i
> End Function
>
>
> In this case, use as =errortrap(formula,#N/A) or
> =errortrap(formula,"",#N/A) (to be explicit). More generally, to pass
> #VALUE!, #NUM! and #DIV/0! errors but replace #N/A, #NULL!, #REF! and
#NAME?
> errors with, say, -1, use =errortrap(formula,-1,#N/A,#NULL!,#REF!,#NAME?)
.
>
> If formula is simple, this will likely slow Excel down. However, if
formula
> involves 6 levels of nested fucntion calls, this will likely speed Excel
up.
>
> >another way assuming
> >
> >=if(countif(d2:d100,b1)=0,"",vlookup(b1,d2:e100,2,0))
>
> Better than evaluating the VLOOKUP twice.
>
> >or
> >
> >=if(countif(d2:d100,b1)=0,"",index(e2:e100,match(b1,d2:d100,0)))
>
> No benefit in this case to using INDEX(.,MATCH(.,.,0)) vs
VLOOKUP(.,.,.,0).
>

UNQUOTE

As long as Microsoft has not taken up the above proposal, we should at least live by a set of reasonable rules shown in the figure below (I posted this figure a while ago at this board, but I can't give here the hyperlink, simply because I couldn't find it via the board's search facility, hence this re-post.)

Microsoft Excel - VLOOKUP.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elpF6F7F8F9F10F16F17=
ABCDEFGHIJKLM1IDLast NameSalary

2010008Smith46,223.00
EXACT MATCH (0 or FALSE, both means the same thing to Excel)

3010002Miller70,000.00
VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)

4010007Young46,223.00

What is better?

5010005Thomas44,662.00
Look UpSalary?

6010001Smith70,000.00
01000270,000.00this

7

70,000.00this, if #N/A must be avoided, because efficient

8

70,000.00inefficient

9

70,000.00inefficient & bad practice

10Commission Table

70,000.00inefficient & bad practice

11Sales%

1200.00%

APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel)
1325000.20%

VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)

1450000.40%

What is better?

1560000.60%

Look UpCommission

1670000.80%

7,400.880.80%this

1780001.00%

0.80%this, if #N/A must be avoided, because efficient; seldom needed
1890001.20%

As above, no ISNA, ISERR, or ISERROR.

19

20SPECIAL NOTE: INDEX/MATCH is faster than VLOOKUP, in particular when the formula must be copied to a huge number of cells.Sheet1
To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-29 16:12 ]

Is there a way to hide the N/A# that a vlook returns

I have a vlookup that pulls back info based on the selection of a drop down box. I'd like the cell to be blank instead of showing N/A# while there is no item selected in the drop down box instead of showing N/A #down every row of the spreadsheet.

Thanks

Hi,

I am having trouble with an excel file that I have created to help me put together automated costings for my business. In a nutshell, I want the spreadsheet to automatically calculate the total weight of items based on the "type" (column C), the "material type/section type" (column D), the "dimensions" (columns F,G,H), "Quantity" (Column J), "Lengths" (Column K). When these columns are filled, it should automatically populate columns L, M, N.

Due to the nature of performing these calculations, I have used a mixture of nested IF functions, SUM functions, and Vlookup functions. All functions I have used appear correct but return only some of the correct values i.e. with some values the functions return #Value instead of the correct value. I believe this to be a problem caused by my vlookup functions which refer to 3 different sheets (within the same workbook) that contain the data lists. They search for a match (text description of item) within the relevant datasheet and then enter the number in the column next to the description.

I have tried all the usual procedures to solve this. I have added FALSE to the 4th argument, I have checked that the format for the data sheets is general, I have removed additional blank spaces, I have checked that the data range is absolute. I cannot seem to solve this problem.

Please see the file attached.

Any help would be greatly appreciated.

Hello,

I have a 'master' worksheet containing the names of 100 pupils in a school and the number of points achieved from each subject they study. I then have subsequent worksheets for each subject containing a breakdown of how points were scored.

I have used a vlookup formula to find the total points scored form each subject worksheets and to then display the total figures on the 'master' table.

The problem I have is that for some subject only 20 students study that particular subject. If a students does not study a subject then the points score of the student in the row above them is displayed instead of 0 points or N/A.

If a student does not study a subject, I would like that cell to be left blank and not display another students data.

This is the formula I have used: =VLOOKUP(A3,Art!A$1:G$59,7)

Is a vlookup the correct way to solve this problem?

Sorry, first post, I hope I have been clear.

I'm trying to find a way to use either Vlookup or Index function to return a value in a table that corresponds with the number at the top of each column.

The issue is, I will be looking up the name on the left of my output table and it corresponds with the number in the top row for each column, however the number from the feeder table is is not in the same format, it's in a column instead of a row. So basically I need to match up the top cell in the column (month number) with the name to the left, but in teh feeder table these will be matched as a Row. Kind of hard to explain.

Further complicating the issue is that not all names have numbers for every month, so some will be #N/A or, ideally, left blank. But I assume I can fix that with an IF statement on my own.

obviously in the real file I'm using there are 12 months not 8, but I just got too lazy to make it that big. Anyway I hope I've explained it well enough.

Anyway thanks in advance.

PS I left the Price column in the feed table blank but I will be making another table like the one for the "quantity" numbers only for the price numbers instead.

Again, thanks in advance. This site is great and all of y'all's time is greatly appreciated.

Thank you,

J


No luck finding an answer? You could always try Google.