Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
Help! I'm using vlookup to merge 2 spreadsheets. My formula is:
=vlookup(A2:A180, Sheet1!A:BQ, 3, FALSE) I want the actual 0s to come up as 0s and the blanks to come up as blanks. I keep getting 0s when the values are actually blank. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
wrote:
My formula is: =vlookup(A2:A180, Sheet1!A:BQ, 3, FALSE) I want the actual 0s to come up as 0s and the blanks to come up as blanks. I keep getting 0s when the values are actually blank. Ostensibly, you should might write: =if(vlookup(A2:A180,Sheet1!A:BQ,3,FALSE)="","", vlookup(A2:A180,Sheet1!A:BQ,3,FALSE)) But.... 1. It should be sufficient to write vlookup(A2,...) instead of vlookup(A2:A180,...). A2 will change to A3, A4 etc as you copy the formula down. The range A2:A180 does not thing for you in this context. 2. That formula can be very inefficient since you are doing a linear lookup twice if the result is nonblank. It would be better to leave the VLOOKUP formula as you wrote it in a helper cell (e.g. B2), and add a column with the formula: =IF(B2="","",B2) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
Thank you. What do you mean by write a column? Can you tell me exactly what I should do?
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
wrote:
What do you mean by write a column? Can you tell me exactly what I should do? Create the following formulas: B2: =vlookup(A2,Sheet1!A:BQ,3,FALSE) C2: =if(B2="","",B2) Copy B2:C2 down through B180:C180. You can hide column B by right-clicking on the column at the top, then clicking on Hide. You can unhide column B by selecting columns A and C (e.g. put A:C in the Name Box), right-clicking the selected columns at the top, and clicking on Unhide. PS: On second thought, for only 179 such formulas, I would be inclined to follow my first suggestion and avoid the helper column (B) by simply writing the following in B2 or C2 and copying down through row 180: =if(vlookup(A2,Sheet1!A:BQ,3,FALSE)="","", vlookup(A2,Sheet1!A:BQ,3,FALSE)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
Try...
=IF(LEN(A2),VLOOKUP(A2,Sheet1!A:BQ,3,FALSE),"") -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
"GS" wrote:
=IF(LEN(A2),VLOOKUP(A2,Sheet1!A:BQ,3,FALSE),"") Salimian wrote: "I want the actual 0s to come up as 0s and the blanks to come up as blanks". I think Salimian is referring to the result of the VLOOKUP. Your formula would work if the result of VLOOKUP were coming from column 1 (kinda useless!). But in this case, it is coming from column 3. The cell in column 3 might be empty even if A2 is not, and vice versa. So it is not sufficient to test A2. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
on 6/29/2012, joeu2004 supposed :
"GS" wrote: =IF(LEN(A2),VLOOKUP(A2,Sheet1!A:BQ,3,FALSE),"") Salimian wrote: "I want the actual 0s to come up as 0s and the blanks to come up as blanks". I think Salimian is referring to the result of the VLOOKUP. Your formula would work if the result of VLOOKUP were coming from column 1 (kinda useless!). But in this case, it is coming from column 3. The cell in column 3 might be empty even if A2 is not, and vice versa. So it is not sufficient to test A2. Good point! I didn't see the point, though, to even bother with the VLOOKUP() if there was nothing to look up in A2. Otherwise, if A2 is empty and column 3 of the lookup range is empty then I expect that zero will be returned since that is a default when refing empty cells. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
"GS" wrote:
I didn't see the point, though, to even bother with the VLOOKUP() if there was nothing to look up in A2. Otherwise, if A2 is empty and column 3 of the lookup range is empty then I expect that zero will be returned since that is a default when refing empty cells. You seem to continue to labor under the false assumption that: (a) A2 might appear blank (Salimian never said that; only that the cell in column Sheet1!C:C that corresponds to the first cell in column Sheet1!A:A that matches A2 might be); and (b) if A2 appears blank, so is the cell in column Sheet1!C:C that corresponds to the first cell in column Sheet1!A:A that appears blank. As for #b, consider =VLOOKUP(A2,{"",1;0,2;1,3;2,4;3,5},2,0), when A2 is empty (no constant and no formula), and its value is the null string (e.g. =""). In the first case, VLOOKUP returns 2. In the second case, VLOOKUP returns 1. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
blank cell return vs. a 0
Like I said.., you made a good point. I was merely explaining my
thinking BEFORE reading your reply. Thus, I NOW no longer "labor under the false assumption..." as you claim here because I 'got it' as soon as I read your reply!<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return blank cell if data cell is blank | Excel Worksheet Functions | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
HELP W/ VBA: SELECT RANGE, ALLCAPS, CELL COLOR, RETURN TO BLANK CELL/PATTERN CELL | Excel Programming | |||
If cell blank return a blank | Excel Worksheet Functions | |||
return a blank cell | Excel Worksheet Functions |