Nested If with VLookup result a 0

T

Tina Hudson

Good day,

I'm getting a 0 (zero) when I use the following IFERROR statement in Excel
2007:

=IFERROR(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE),"")

I originally used just the VLookup Function, but when the reference cell
(A7) was blank, I got a #N/A error. So, I added the IFERROR function. Now
I'm getting a 0 (zero) as my result.

I'm confused because I've used this exact expression throughout the
worksheet and didn't have any trouble, except for 2 fields - one a number
field (which I sort of get, but not really) and the other field a text field.

I tried changing to the following, but I get "False" as the result:

=IF(IFERROR(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE),""),IF(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE)=0,"",(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE))))

Any help will be most appreciated!
 
T

T. Valko

=IFERROR(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE),"")

If you're getting a result of 0 it means either the corresponding cell in
the table_array is empty or is numeric 0.

If the value returned by the lookup is supposed to be a text value you can
do this:

=IFERROR(T(VLOOKUP(A7,Reports!$A$1:$V$1000,6,0)),"")
 
T

Tina Hudson

Biff,

The corresponding cell in the table array is a date.
--
Thanks,
Tina Hudson


T. Valko said:
=IFERROR(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE),"")

If you're getting a result of 0 it means either the corresponding cell in
the table_array is empty or is numeric 0.

If the value returned by the lookup is supposed to be a text value you can
do this:

=IFERROR(T(VLOOKUP(A7,Reports!$A$1:$V$1000,6,0)),"")
 
T

T. Valko

The corresponding cell in the table array is a date.

Will there only be one (or none) instance of the lookup_value A7 in
Reports!A1:A1000?

If so, try this:

=IF(SUMIF(Reports!$A$1:$A$1000,A7,Reports!$F$1:$F$1000),VLOOKUP(A7,Reports!$A$1:$V$1000,6,0),"")

--
Biff
Microsoft Excel MVP


Tina Hudson said:
Biff,

The corresponding cell in the table array is a date.
 
T

Tina Hudson

Biff,

Let me back up and explain what I'm trying to do. I have a spreadsheet with
about 6 worksheets. The first worksheet (Reports) has information about
reports we receive through our intake and has columns with the date the
report was received (col b), the Case Name (col a), case number (col f), etc.

I want to be able to populate other worksheets in the spreadsheet with
specific information from the first worksheet (Reports), so that we don't
have to retype any information in the other worksheets.

So, on the Treatment worksheet, I have Case Name (col a), and report date
(col b), case number (col f), etc.

With the following formula, I easily can pick up the report date, but the
result for case number is 0:

=IFERROR(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE),"")

(If A7 is blank, then I want the column under Case Number to be blank, not
have #N/A in it, which is what I get if I don't include IFERROR function.

I didn't understand about sumif because I'm not summing - I want the exact
data in the cell to be copied to the other worksheet.
 
T

T. Valko

Ok, try this:

=IFERROR(IF(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE)=0,"",VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE)),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top