| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If with VLookup result a 0
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,FALS E),""),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! -- Thanks, Tina Hudson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If with VLookup result a 0
=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))," ") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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,FALS E),""),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! -- Thanks, Tina Hudson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If with VLookup result a 0
Biff,
The corresponding cell in the table array is a date. -- Thanks, Tina Hudson "T. Valko" wrote: =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))," ") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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,FALS E),""),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! -- Thanks, Tina Hudson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If with VLookup result a 0
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" wrote in message ... Biff, The corresponding cell in the table array is a date. -- Thanks, Tina Hudson "T. Valko" wrote: =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))," ") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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,FALS E),""),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! -- Thanks, Tina Hudson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If with VLookup result a 0
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. -- Thanks, Tina Hudson "T. Valko" wrote: 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" wrote in message ... Biff, The corresponding cell in the table array is a date. -- Thanks, Tina Hudson "T. Valko" wrote: =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))," ") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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,FALS E),""),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! -- Thanks, Tina Hudson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If with VLookup result a 0
Ok, try this:
=IFERROR(IF(VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALS E)=0,"",VLOOKUP(A7,Reports!$A$1:$V$1000,6,FALSE)), "") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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. -- Thanks, Tina Hudson "T. Valko" wrote: 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" wrote in message ... Biff, The corresponding cell in the table array is a date. -- Thanks, Tina Hudson "T. Valko" wrote: =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))," ") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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,FALS E),""),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! -- Thanks, Tina Hudson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Nested If with VLookup result a 0
Hi,
=if(A7="","",IFERROR(VLOOKUP(A7,Reports!$A$1:$V$10 00,6,FALSE),"")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Tina Hudson" wrote in message ... 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. -- Thanks, Tina Hudson "T. Valko" wrote: 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" wrote in message ... Biff, The corresponding cell in the table array is a date. -- Thanks, Tina Hudson "T. Valko" wrote: =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))," ") -- Biff Microsoft Excel MVP "Tina Hudson" wrote in message ... 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,FALS E),""),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! -- Thanks, Tina Hudson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested if with an unwanted false result | Excel Discussion (Misc queries) | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| vlookup shows result one cell above the expected result | Excel Worksheet Functions | |||
| vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
| how do I assign a symbol to the result of a part of a nested IF s. | Excel Worksheet Functions | |||
Hybrid Mode
