Home 
Search 
Today's Posts 
#1




Vlookup finds a blank, but returns a zero  HELP!
I have a chart, and several series contain data that comes from elsewhere
using a VLOOKUP. However, when the VLOOKUP finds a blank, it returns a zero. I want the chart to interpolate the data, but Excel charts can only interpolate blanks, not zeroes. I've used ISBLANK with the VLOOKUP to return what appears to be a blank, but the chart still sees it as a zero, and thus will not interpolate. I'd like to know if there's a way to set the value of a cell to NULL, because "" doesn't work... 
#2




You have a couple of options for a workaround: one is to nest the
VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup value = "","",normal vlookup) Another way to do it is to nest a MATCH function inside an INDEX function, best shown by example: suppose values in A1:A4 are penny nickel dime quarter ....and suppose values in B1:B4 are ..01 ..05 ..1 ..25 Copy the A1:A4 values into D14 (to show that the values can appear in a different cell, must be spelled exactly, etc). Then in cell E1, enter this formula: =INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2) The English translation is: find an exact match of D1 in the range A1:A4 (which becomes a row reference for the INDEX function); in the range A1:B4 go to the row specified by the MATCH function and the column number supplied (i.e., 2). 
#3




Insert an additional column before the column where you have your
VLOOKUP formula. Suppose that column Y is the column housing those retrieval formula: In X2 enter & copy down: =IF(Y2=0,#N/A,Y2) Exclude column Y from charts. flummoxed wrote: I have a chart, and several series contain data that comes from elsewhere using a VLOOKUP. However, when the VLOOKUP finds a blank, it returns a zero. I want the chart to interpolate the data, but Excel charts can only interpolate blanks, not zeroes. I've used ISBLANK with the VLOOKUP to return what appears to be a blank, but the chart still sees it as a zero, and thus will not interpolate. I'd like to know if there's a way to set the value of a cell to NULL, because "" doesn't work... 
#4




I appreciate your suggestion, but I've already nested the VLOOKUP in an IF
statement, similar to the way you've suggested. Mine follows this pattern: IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but still carries a value of zero. in your example, if you substitute a blank for the value of a nickel instead of .05, then chart the results column (with interpolation for blank cells), you'll see my dilemma. It won't interpolate, because it has a zero value. The INDEX MATCH combination provides the same result as a VLOOKUP. "Dave O" wrote: You have a couple of options for a workaround: one is to nest the VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup value = "","",normal vlookup) Another way to do it is to nest a MATCH function inside an INDEX function, best shown by example: suppose values in A1:A4 are penny nickel dime quarter ....and suppose values in B1:B4 are ..01 ..05 ..1 ..25 Copy the A1:A4 values into D14 (to show that the values can appear in a different cell, must be spelled exactly, etc). Then in cell E1, enter this formula: =INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2) The English translation is: find an exact match of D1 in the range A1:A4 (which becomes a row reference for the INDEX function); in the range A1:B4 go to the row specified by the MATCH function and the column number supplied (i.e., 2). 
#5




The #N/A error is very friendly to making graphs show null.
So, simply revise your formulas to replace the null [ "" ] with "#N/A" (no quotes).  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "flummoxed" wrote in message ... I appreciate your suggestion, but I've already nested the VLOOKUP in an IF statement, similar to the way you've suggested. Mine follows this pattern: IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but still carries a value of zero. in your example, if you substitute a blank for the value of a nickel instead of .05, then chart the results column (with interpolation for blank cells), you'll see my dilemma. It won't interpolate, because it has a zero value. The INDEX MATCH combination provides the same result as a VLOOKUP. "Dave O" wrote: You have a couple of options for a workaround: one is to nest the VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup value = "","",normal vlookup) Another way to do it is to nest a MATCH function inside an INDEX function, best shown by example: suppose values in A1:A4 are penny nickel dime quarter ....and suppose values in B1:B4 are ..01 ..05 ..1 ..25 Copy the A1:A4 values into D14 (to show that the values can appear in a different cell, must be spelled exactly, etc). Then in cell E1, enter this formula: =INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2) The English translation is: find an exact match of D1 in the range A1:A4 (which becomes a row reference for the INDEX function); in the range A1:B4 go to the row specified by the MATCH function and the column number supplied (i.e., 2). 
#6




Thanks! That worked like a charm  Excel sometimes works in mysterious
ways, doesn't it? Sorry if I didn't keep a response within the group, but this is my initial forray into this medium. "RagDyer" wrote: The #N/A error is very friendly to making graphs show null. So, simply revise your formulas to replace the null [ "" ] with "#N/A" (no quotes).  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "flummoxed" wrote in message ... I appreciate your suggestion, but I've already nested the VLOOKUP in an IF statement, similar to the way you've suggested. Mine follows this pattern: IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but still carries a value of zero. in your example, if you substitute a blank for the value of a nickel instead of .05, then chart the results column (with interpolation for blank cells), you'll see my dilemma. It won't interpolate, because it has a zero value. The INDEX MATCH combination provides the same result as a VLOOKUP. "Dave O" wrote: You have a couple of options for a workaround: one is to nest the VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup value = "","",normal vlookup) Another way to do it is to nest a MATCH function inside an INDEX function, best shown by example: suppose values in A1:A4 are penny nickel dime quarter ....and suppose values in B1:B4 are ..01 ..05 ..1 ..25 Copy the A1:A4 values into D14 (to show that the values can appear in a different cell, must be spelled exactly, etc). Then in cell E1, enter this formula: =INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2) The English translation is: find an exact match of D1 in the range A1:A4 (which becomes a row reference for the INDEX function); in the range A1:B4 go to the row specified by the MATCH function and the column number supplied (i.e., 2). 
#7




You DID keep your response within the news group,
AND, thanks for the feedback.  Regards, RD  Please keep all correspondence within the Group, so all may benefit !  "flummoxed" wrote in message news Thanks! That worked like a charm  Excel sometimes works in mysterious ways, doesn't it? Sorry if I didn't keep a response within the group, but this is my initial forray into this medium. "RagDyer" wrote: The #N/A error is very friendly to making graphs show null. So, simply revise your formulas to replace the null [ "" ] with "#N/A" (no quotes).  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "flummoxed" wrote in message ... I appreciate your suggestion, but I've already nested the VLOOKUP in an IF statement, similar to the way you've suggested. Mine follows this pattern: IF(ISBLANK(VLOOKUP),"",VLOOKUP) I get a result that appears as a blank, but still carries a value of zero. in your example, if you substitute a blank for the value of a nickel instead of .05, then chart the results column (with interpolation for blank cells), you'll see my dilemma. It won't interpolate, because it has a zero value. The INDEX MATCH combination provides the same result as a VLOOKUP. "Dave O" wrote: You have a couple of options for a workaround: one is to nest the VLOOKUP into an IF formula, so the logic flow would be: IF(vlookup value = "","",normal vlookup) Another way to do it is to nest a MATCH function inside an INDEX function, best shown by example: suppose values in A1:A4 are penny nickel dime quarter ....and suppose values in B1:B4 are ..01 ..05 ..1 ..25 Copy the A1:A4 values into D14 (to show that the values can appear in a different cell, must be spelled exactly, etc). Then in cell E1, enter this formula: =INDEX($A$1:$B$4,MATCH(D1,$A$1:$A$4,0),2) The English translation is: find an exact match of D1 in the range A1:A4 (which becomes a row reference for the INDEX function); in the range A1:B4 go to the row specified by the MATCH function and the column number supplied (i.e., 2). 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C.  Excel Discussion (Misc queries)  
Non Blank  Blank Cells????  Excel Discussion (Misc queries)  
I need a VLOOKUP to display 0 or blank instead of N/A  Excel Discussion (Misc queries)  
Using Jet to read excel file returns blank for last cell  sometim  Excel Discussion (Misc queries)  
Vlookup returns incorrect match  Excel Discussion (Misc queries) 