Is there a way to do a vlookup but return no value at all for #NA situations.
For example on a numeric field, If there is no match, I do not want a cell
entry at all, not even a blank. I've tried doing multiple scenarios... but
no luck.

The reason is that I need to report a blank financial value in a pivot
report to identify no match was found in order to differentiate between
legitimate 0 values and/or division errors.

Any non-numeric value automatically turns into a 0 in the pivot report -
unless the cell was completely blank in the first place and not populated. f

I tried leaving the actuals #N/A error in the cell and I though this would
work because the #N/A at least appers as-is in the pivot reports, however the
totalling doesn't work in the reports.

=IF(ISNA(VLOOKUP([your vlookup])),"",VLOOKUP([your vlookup]))

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Is there a way to do a vlookup but return no value at all for #NA situations.
For example on a numeric field, If there is no match, I do not want a cell
entry at all, not even a blank. I've tried doing multiple scenarios... but
no luck.

The reason is that I need to report a blank financial value in a pivot
report to identify no match was found in order to differentiate between
legitimate 0 values and/or division errors.

Any non-numeric value automatically turns into a 0 in the pivot report -
unless the cell was completely blank in the first place and not populated. f

I tried leaving the actuals #N/A error in the cell and I though this would
work because the #N/A at least appers as-is in the pivot reports, however the
totalling doesn't work in the reports.

Thanks, but I had already tried that. The " " puts a blank in the cell which
translates into a 0 value on reports. I would like to leave the cell value
completely alone if there is no match, but don't know if there is a way to do
that using vlookup. Or is there a way to automate deleting a cell value is a
value gets pull in?

When I use the formula you mention, I actually go in manually and delete the
" " value to get the results I want, but want to automate this somehow.



"Dave F" wrote:

=IF(ISNA(VLOOKUP([your vlookup])),"",VLOOKUP([your vlookup]))

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Is there a way to do a vlookup but return no value at all for #NA situations.
For example on a numeric field, If there is no match, I do not want a cell
entry at all, not even a blank. I've tried doing multiple scenarios... but
no luck.

The reason is that I need to report a blank financial value in a pivot
report to identify no match was found in order to differentiate between
legitimate 0 values and/or division errors.

Any non-numeric value automatically turns into a 0 in the pivot report -
unless the cell was completely blank in the first place and not populated. f

I tried leaving the actuals #N/A error in the cell and I though this would
work because the #N/A at least appers as-is in the pivot reports, however the
totalling doesn't work in the reports.

I will just clear out the values manually.

"MLK" wrote:

Thanks, but I had already tried that. The " " puts a blank in the cell which
translates into a 0 value on reports. I would like to leave the cell value
completely alone if there is no match, but don't know if there is a way to do
that using vlookup. Or is there a way to automate deleting a cell value is a
value gets pull in?

When I use the formula you mention, I actually go in manually and delete the
" " value to get the results I want, but want to automate this somehow.



"Dave F" wrote:

=IF(ISNA(VLOOKUP([your vlookup])),"",VLOOKUP([your vlookup]))

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Is there a way to do a vlookup but return no value at all for #NA situations.
For example on a numeric field, If there is no match, I do not want a cell
entry at all, not even a blank. I've tried doing multiple scenarios... but
no luck.

The reason is that I need to report a blank financial value in a pivot
report to identify no match was found in order to differentiate between
legitimate 0 values and/or division errors.

Any non-numeric value automatically turns into a 0 in the pivot report -
unless the cell was completely blank in the first place and not populated. f

I tried leaving the actuals #N/A error in the cell and I though this would
work because the #N/A at least appers as-is in the pivot reports, however the
totalling doesn't work in the reports.

Hi MLK
did you notice that Dave said to try "" (double quotes with no space) and
you said you had tried " " (Double quotes with a space)? The space makes a
difference, you actually put an invisible space in the cell with one and put
nothing in the cell with Dave's method...Just wanted to make sure you had
noticed and had already tried that.
SongBear

"MLK" wrote:

Thanks, but I had already tried that. The " " puts a blank in the cell which
translates into a 0 value on reports. I would like to leave the cell value
completely alone if there is no match, but don't know if there is a way to do
that using vlookup. Or is there a way to automate deleting a cell value is a
value gets pull in?

When I use the formula you mention, I actually go in manually and delete the
" " value to get the results I want, but want to automate this somehow.



"Dave F" wrote:

=IF(ISNA(VLOOKUP([your vlookup])),"",VLOOKUP([your vlookup]))

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Is there a way to do a vlookup but return no value at all for #NA situations.
For example on a numeric field, If there is no match, I do not want a cell
entry at all, not even a blank. I've tried doing multiple scenarios... but
no luck.

The reason is that I need to report a blank financial value in a pivot
report to identify no match was found in order to differentiate between
legitimate 0 values and/or division errors.

Any non-numeric value automatically turns into a 0 in the pivot report -
unless the cell was completely blank in the first place and not populated. f

I tried leaving the actuals #N/A error in the cell and I though this would
work because the #N/A at least appers as-is in the pivot reports, however the
totalling doesn't work in the reports.

Thanks, but I tried everything.

I actually went online to google and found some postings that state you can
never return a non-value in a formula... something will always be returned.
I'm trying to return nothing under certain conditions and returning a null
value is like saying return a zero value, which will not work for what I need
to do in the pivot reports.

Thanks again.


"SongBear" wrote:

Hi MLK
did you notice that Dave said to try "" (double quotes with no space) and
you said you had tried " " (Double quotes with a space)? The space makes a
difference, you actually put an invisible space in the cell with one and put
nothing in the cell with Dave's method...Just wanted to make sure you had
noticed and had already tried that.
SongBear

"MLK" wrote:

Thanks, but I had already tried that. The " " puts a blank in the cell which
translates into a 0 value on reports. I would like to leave the cell value
completely alone if there is no match, but don't know if there is a way to do
that using vlookup. Or is there a way to automate deleting a cell value is a
value gets pull in?

When I use the formula you mention, I actually go in manually and delete the
" " value to get the results I want, but want to automate this somehow.



"Dave F" wrote:

=IF(ISNA(VLOOKUP([your vlookup])),"",VLOOKUP([your vlookup]))

Dave
--
Brevity is the soul of wit.


"MLK" wrote:

Is there a way to do a vlookup but return no value at all for #NA situations.
For example on a numeric field, If there is no match, I do not want a cell
entry at all, not even a blank. I've tried doing multiple scenarios... but
no luck.

The reason is that I need to report a blank financial value in a pivot
report to identify no match was found in order to differentiate between
legitimate 0 values and/or division errors.

Any non-numeric value automatically turns into a 0 in the pivot report -
unless the cell was completely blank in the first place and not populated. f

I tried leaving the actuals #N/A error in the cell and I though this would
work because the #N/A at least appers as-is in the pivot reports, however the
totalling doesn't work in the reports.