Nested If with VLookup result a 0

[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. 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)),"")

 

[Tina Hudson]

Biff,

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

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

Biff,

The corresponding cell in the table array is a date.

 

[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. 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)),"")

 

[Ashish Mathur]

Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Tina Hudson]

Thanks Biff - worked beautifully.
Have a good one.
Tina

 

[T. Valko]

You're welcome. Thanks for the feedback!