blank cell return vs. a 0

[salimian.parissa]

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.

 

[joeu2004]

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)

 

[salimian.parissa]

Thank you. What do you mean by write a column? Can you tell me exactly what I should do?

 

[joeu2004]

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))

 

[GS]

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

 

[joeu2004]

=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.

 

[GS]

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

 

[joeu2004]

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.

 

[GS]

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