VLOOKUP with a blank cell
 

I am trying to do a vlookup that looks for the value in the cells in column A
in my table. However, some of the cells in column A are blank. My lookup
table has a blank in the first column and a code value to be returned in the
4th column. When I try to do the lookup and it encounters a blank cell, I get
a #N/A result instead of the code.
Here is the formula I have now.
=VLOOKUP(A13,$F$3:$I$13,4,FALSE)

Or should I use this as an alternate formula:

=IF(A13='"","L",VLOOKUP(A13,$F$3:$I$13,4,FALSE))

The formula above works, but I would like to use just a vlookup.

Any way around this or should I just use the 2nd formula?

Thanks

VLOOKUP with a blank cell
 

I asked a similar question:

http://groups.google.com/group/micro...1?q=gsnu2007xx


--
Gary''s Student - gsnu200817


"Iriemon" wrote:

I am trying to do a vlookup that looks for the value in the cells in column A
in my table. However, some of the cells in column A are blank. My lookup
table has a blank in the first column and a code value to be returned in the
4th column. When I try to do the lookup and it encounters a blank cell, I get
a #N/A result instead of the code.
Here is the formula I have now.
=VLOOKUP(A13,$F$3:$I$13,4,FALSE)

Or should I use this as an alternate formula:

=IF(A13='"","L",VLOOKUP(A13,$F$3:$I$13,4,FALSE))

The formula above works, but I would like to use just a vlookup.

Any way around this or should I just use the 2nd formula?

Thanks

VLOOKUP with a blank cell
 

One way perhaps...

=if(iserror(vlookup(A13,$F$3:$I$13,4,FALSE)),"",vl ookup(A13,$F$3:$I$13,4,FALSE))

"Iriemon" wrote:

I am trying to do a vlookup that looks for the value in the cells in column A
in my table. However, some of the cells in column A are blank. My lookup
table has a blank in the first column and a code value to be returned in the
4th column. When I try to do the lookup and it encounters a blank cell, I get
a #N/A result instead of the code.
Here is the formula I have now.
=VLOOKUP(A13,$F$3:$I$13,4,FALSE)

Or should I use this as an alternate formula:

=IF(A13='"","L",VLOOKUP(A13,$F$3:$I$13,4,FALSE))

The formula above works, but I would like to use just a vlookup.

Any way around this or should I just use the 2nd formula?

Thanks

VLOOKUP with a blank cell
 

I think you are stuck with the IF
But how about
=IF(A13='"",I3,VLOOKUP(A13,$F$3:$I$13,4,FALSE))
so that you can change the table without worrying about the IF formula?
(I think I3 is the correct reference - check it!)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Iriemon" wrote in message
...
I am trying to do a vlookup that looks for the value in the cells in column
A
in my table. However, some of the cells in column A are blank. My lookup
table has a blank in the first column and a code value to be returned in
the
4th column. When I try to do the lookup and it encounters a blank cell, I
get
a #N/A result instead of the code.
Here is the formula I have now.
=VLOOKUP(A13,$F$3:$I$13,4,FALSE)

Or should I use this as an alternate formula:

=IF(A13='"","L",VLOOKUP(A13,$F$3:$I$13,4,FALSE))

The formula above works, but I would like to use just a vlookup.

Any way around this or should I just use the 2nd formula?

Thanks

VLOOKUP with a blank cell
 

Iriemon,
Try the following:
=IF(ISERROR(VLOOKUP(A13,$F$3:$I$13,4,FALSE)),"",VL OOKUP(A13,$F$3:$I$13,4,FALSE))
OR
=IF(ISNA(VLOOKUP(A13,$F$3:$I$13,4,FALSE)),"",VLOOK UP(A13,$F$3:$I$13,4,FALSE))

<<Should be all one line:Watch for Word Wrap in News Reader

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule
<<Previous Text Snipped to Save Bandwidth When Appropriate


"Iriemon" wrote in message
...
I am trying to do a vlookup that looks for the value in the cells in column A
in my table. However, some of the cells in column A are blank. My lookup
table has a blank in the first column and a code value to be returned in the
4th column. When I try to do the lookup and it encounters a blank cell, I get
a #N/A result instead of the code.
Here is the formula I have now.
=VLOOKUP(A13,$F$3:$I$13,4,FALSE)

Or should I use this as an alternate formula:

=IF(A13='"","L",VLOOKUP(A13,$F$3:$I$13,4,FALSE))

The formula above works, but I would like to use just a vlookup.

Any way around this or should I just use the 2nd formula?

Thanks

VLOOKUP with a blank cell
 

Thanks Bernard! I like the change you suggested too, makes it easy to change
values without changing the formula.

J

"Bernard Liengme" wrote:

I think you are stuck with the IF
But how about
=IF(A13='"",I3,VLOOKUP(A13,$F$3:$I$13,4,FALSE))
so that you can change the table without worrying about the IF formula?
(I think I3 is the correct reference - check it!)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Iriemon" wrote in message
...
I am trying to do a vlookup that looks for the value in the cells in column
A
in my table. However, some of the cells in column A are blank. My lookup
table has a blank in the first column and a code value to be returned in
the
4th column. When I try to do the lookup and it encounters a blank cell, I
get
a #N/A result instead of the code.
Here is the formula I have now.
=VLOOKUP(A13,$F$3:$I$13,4,FALSE)

Or should I use this as an alternate formula:

=IF(A13='"","L",VLOOKUP(A13,$F$3:$I$13,4,FALSE))

The formula above works, but I would like to use just a vlookup.

Any way around this or should I just use the 2nd formula?

Thanks