#N/A error when lookup value is blank and lookup vector has a blank with VLOOKUP

E

Excel_VBA

New Member
Joined
Dec 19, 2009
Messages
42
I am using a VLOOKUP on a 2 column table array. Each value in column 1 has a corresponding value in column 2. However there is a case where in column1 I have a blank cell with the corresponding value for a cell that is blank cell.

When my VLOOKUP formula encounters a blank cell for the Lookup value it returns a #N/A.

In short, I want the VLOOKUP to say, when the lookup value is a blank cell, go to the table array, find the blank cell in column one and return the corresonding value in column 2 of the table array.

Is it possible to use a blank cell as a lookup value? Is this why I have the #N/A error?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Excel_VBA said:
I am using a VLOOKUP on a 2 column table array. Each value in column 1 has a corresponding value in column 2. However there is a case where in column1 I have a blank cell with the corresponding value for a cell that is blank cell.

When my VLOOKUP formula encounters a blank cell for the Lookup value it returns a #N/A.

In short, I want the VLOOKUP to say, when the lookup value is a blank cell, go to the table array, find the blank cell in column one and return the corresonding value in column 2 of the table array.

Is it possible to use a blank cell as a lookup value? Is this why I have the #N/A error?
Click to expand...
If I understand what you're asking...

You have a lookup table something like this...

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">22</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">22</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">97</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">85</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">55</TD></TR></TBODY></TABLE>

And you have a lookup formula something like this...

VLOOKUP(A1,E1:F6,2,0)

However, if cell A1 (the lookup value) is empty instead of the #N/A error you want the result from cell F6 of the lookup table.

Is this what you want?

If so, then a formula like this:

=IF(A1="",F6,VLOOKUP(A1,E1:F6,2,0))
 
Upvote 0
Try this...
Instead of using a blank cell in your lookup table, use a single apostrophe (')

Then use this kind of VLOOKUP approach:
Code: 
=VLOOKUP(""&A1,C1:D4,2,0)
Is that something you can work with?
 
Upvote 0
T. Valko said:
If I understand what you're asking...

You have a lookup table something like this...

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">22</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">22</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">97</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">85</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">55</TD></TR></TBODY></TABLE>

And you have a lookup formula something like this...

VLOOKUP(A1,E1:F6,2,0)

However, if cell A1 (the lookup value) is empty instead of the #N/A error you want the result from cell F6 of the lookup table.

Is this what you want?

If so, then a formula like this:

=IF(A1="",F6,VLOOKUP(A1,E1:F6,2,0))
Click to expand...

Yes you're right. If I encounter a blank in column A, it should return '55.'
Here is my VLOOKUP formula: VLOOKUP(J10,$AP$2:$AQ$12,2,0

Column J is my lookup vector, the array is AP:AQ. I have the VLOOKUP copied down many rows. I don't want to test if column J is blank. For each row, I want the VLOOKUP to pick up whatever is in column J (whether it is a blank or not) then return the corresponding value in the array.

Here is something I just noticed. While the values in column J appear to blank, I tested the ISBLANK function on those cells and it actually returned false. When I click on formula bar, the cursor is all the way to the left so there are not any spaces in the formula bar. There are not any formulas either. So now it seems like column J really isn't blank even though it sure seems like it.

The question now is, what exactly is in column J, that is causing Excel to think column J is not blank? I think the answer is in there. Any ideas?
 
Upvote 0
Ron Coderre said:
Try this...
Instead of using a blank cell in your lookup table, use a single apostrophe (')

Then use this kind of VLOOKUP approach:

=VLOOKUP(""&A1,C1:D4,2,0)

Is that something you can work with?
Click to expand...
That would cause a problem if the look values are numeric values.
 
Upvote 0
Excel_VBA said:
Here is something I just noticed. While the values in column J appear to blank, I tested the ISBLANK function on those cells and it actually returned false. When I click on formula bar, the cursor is all the way to the left so there are not any spaces in the formula bar. There are not any formulas either. So now it seems like column J really isn't blank even though it sure seems like it.

The question now is, what exactly is in column J, that is causing Excel to think column J is not blank? I think the answer is in there. Any ideas?
Click to expand...

I would think 1 of 2 possiblities...

1. Is the data importd from another application like Access,or the Web?
That can be problematic

2. The values USED to be formulas that resulted in ""
And you have since done the copy / paste special / values on that column.

Problem is, "" is NOT Blank, it is a Null Text String.
And even doing the copy / paste special does not remove that.


Now what to do about it depends on your data.
Is the data numeric or text?
 
Upvote 0
Excel_VBA said:
Yes you're right. If I encounter a blank in column A, it should return '55.'
Here is my VLOOKUP formula: VLOOKUP(J10,$AP$2:$AQ$12,2,0

Column J is my lookup vector, the array is AP:AQ. I have the VLOOKUP copied down many rows. I don't want to test if column J is blank. For each row, I want the VLOOKUP to pick up whatever is in column J (whether it is a blank or not) then return the corresponding value in the array.

Here is something I just noticed. While the values in column J appear to blank, I tested the ISBLANK function on those cells and it actually returned false. When I click on formula bar, the cursor is all the way to the left so there are not any spaces in the formula bar. There are not any formulas either. So now it seems like column J really isn't blank even though it sure seems like it.

The question now is, what exactly is in column J, that is causing Excel to think column J is not blank? I think the answer is in there. Any ideas?
Click to expand...
Let's assume one of those blank cells is J10. What result do you get from these formulas:

=LEN(J10)

=CODE(J10)
 
Upvote 0
jonmo1 said:
I would think 1 of 2 possiblities...

1. Is the data importd from another application like Access,or the Web?
That can be problematic

2. The values USED to be formulas that resulted in ""
And you have since done the copy / paste special / values on that column.

Problem is, "" is NOT Blank, it is a Null Text String.
And even doing the copy / paste special does not remove that.


Now what to do about it depends on your data.
Is the data numeric or text?
Click to expand...

This is query is coming out of Oracle/PeopleSoft.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Lookup help! How to return a specific line of text when return value is blank
Replies
2
Views
270
Noswen869
N
T
  • Solved
VLOOKUP SUM with blank values
Replies
4
Views
310
AlanY
A
C
  • Solved
If VLOOKUP is @NA check Table 2
Replies
9
Views
567
Joe4
Joe4
R
  • Question
VLOOKUP formula using the TEXT function as the "lookup value"
Replies
1
Views
980
awoohaw
awoohaw
O
  • Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
2K
nemmi69
nemmi69

Forum statistics

Threads
1,217,361
Messages
6,136,103
Members
449,991
Latest member
IslandofBDA

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top