Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP returns 0 (zero) when lookup cell is blank | Excel Discussion (Misc queries) | |||
Vlookup information from blank cell | Excel Worksheet Functions | |||
Vlookup return 0 when cell is blank | Excel Worksheet Functions | |||
VLOOKUP if a Cell is not Blank | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |