| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Vlook up returning N?A
We are using the following formula and it returns N/A
=IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
Hi Andy
Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
Andy,
It is returning NA because it didn't find the value from cell A79. Generally, wrap your first check in an ISERROR function: =IF($A790,IF(ISERROR(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") HTH, Bernie MS Excel MVP "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
=IF($A790, IF(COUNTIF('BO Output'!$A$3:$A$2002,$A79),VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE),"")) "andy" wrote: We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
In some cases it is returning <blank, as desired, and in other cases it
returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
You might have an empty cell in your table which is being found
correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01*pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
The ISNA is working fine - it's the vlookup. I am using this:
=VLOOKUP($A8,'Plan & Track'!$A$3:$AN$2002,9,FALSE) I am using vlookups on two worksheets - it returns "0" from one worksheet, and <blank from the other. I don't see any differences in the cells I am trying to find. Any ideas? "Mark" wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
yes, it is finding the cell - if I plug in a value it works fine. How can I
force it to find a blank instead of a 0? Do I need to format the cells? "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
You could apply conditional formatting to the cell such that if the
content is 0 then use a foreground colour of white (which on a white background will make the cell appear blank). Hope this helps. Pete On Jan 18, 7:16*pm, Mark wrote: yes, it is finding the cell - if I plug in a value it works fine. How can I force it to find a blank instead of a 0? Do I need to format the cells? "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
good idea, but - we are uploading the result into a db2 table, so we don't
want zeroes. Also, some of the legitimate data is "0". "Pete_UK" wrote: You could apply conditional formatting to the cell such that if the content is 0 then use a foreground colour of white (which on a white background will make the cell appear blank). Hope this helps. Pete On Jan 18, 7:16 pm, Mark wrote: yes, it is finding the cell - if I plug in a value it works fine. How can I force it to find a blank instead of a 0? Do I need to format the cells? "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
If zero is a legitimite value, why do you want to get rid of it?
You could wrap what you have already in a formula along the lines of: =IF(existing_formula=0,"",existing_formula) to make sure that a blank is returned instead of zero, but your formula will become unwieldy. If you are using the version that Roger posted earlier, then you could amend it to this: =IF($A79<=0,"",IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)),"",IF(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)=0,"",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,0)))) which is slightly less cumbersome. Hope this helps. Pete On Jan 18, 7:38*pm, Mark wrote: good idea, but - we are uploading the result into a db2 table, so we don't want zeroes. Also, some of the legitimate data is "0". "Pete_UK" wrote: You could apply conditional formatting to the cell such that if the content is 0 then use a foreground colour of white (which on a white background will make the cell appear blank). Hope this helps. Pete On Jan 18, 7:16 pm, Mark wrote: yes, it is finding the cell - if I plug in a value it works fine. How can I force it to find a blank instead of a 0? Do I need to format the cells? "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
Thanks for the effort, but that did not work, either. The function is
returning zeroes for all blanks, and also returning a zero for a zero value. This version blanks out the #N/As, which is good, but it also strips out all of the legitimate zeroes. "Pete_UK" wrote: If zero is a legitimite value, why do you want to get rid of it? You could wrap what you have already in a formula along the lines of: =IF(existing_formula=0,"",existing_formula) to make sure that a blank is returned instead of zero, but your formula will become unwieldy. If you are using the version that Roger posted earlier, then you could amend it to this: =IF($A79<=0,"",IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)),"",IF(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)=0,"",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,0)))) which is slightly less cumbersome. Hope this helps. Pete On Jan 18, 7:38 pm, Mark wrote: good idea, but - we are uploading the result into a db2 table, so we don't want zeroes. Also, some of the legitimate data is "0". "Pete_UK" wrote: You could apply conditional formatting to the cell such that if the content is 0 then use a foreground colour of white (which on a white background will make the cell appear blank). Hope this helps. Pete On Jan 18, 7:16 pm, Mark wrote: yes, it is finding the cell - if I plug in a value it works fine. How can I force it to find a blank instead of a 0? Do I need to format the cells? "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
So, you want zero to be returned if that is what is in the table, but
if the table entry is blank then you want a blank to be returned? If so, try this modification: =IF($A79<=0,"",IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)),"",IF(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)="","",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,0)))) Hope this helps. Pete On Jan 18, 8:55*pm, Mark wrote: Thanks for the effort, but that did not work, either. The function is returning zeroes for all blanks, and also returning a zero for a zero value. This version blanks out the #N/As, which is good, but it also strips out all of the legitimate zeroes. "Pete_UK" wrote: If zero is a legitimite value, why do you want to get rid of it? You could wrap what you have already in a formula along the lines of: =IF(existing_formula=0,"",existing_formula) to make sure that a blank is returned instead of zero, but your formula will become unwieldy. If you are using the version that Roger posted earlier, then you could amend it to this: =IF($A79<=0,"",IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)),"",IF(VLOOKUP($A79,'BO Output'!$A$3:$P $2002,4,0)=0,"",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,0)))) which is slightly less cumbersome. Hope this helps. Pete On Jan 18, 7:38 pm, Mark wrote: good idea, but - we are uploading the result into a db2 table, so we don't want zeroes. Also, some of the legitimate data is "0". "Pete_UK" wrote: You could apply conditional formatting to the cell such that if the content is 0 then use a foreground colour of white (which on a white background will make the cell appear blank). Hope this helps. Pete On Jan 18, 7:16 pm, Mark wrote: yes, it is finding the cell - if I plug in a value it works fine. How can I force it to find a blank instead of a 0? Do I need to format the cells? "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A-Resolved
Pete_UK thanks for the help, mark's a coworker and i have new spreadsheet
to use! You rock! -- andy "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text - |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
Thanks for your reply!
-- andy "Bernie Deitrick" wrote: Andy, It is returning NA because it didn't find the value from cell A79. Generally, wrap your first check in an ISERROR function: =IF($A790,IF(ISERROR(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") HTH, Bernie MS Excel MVP "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A
thanks for your reply.
-- andy "Teethless mama" wrote: =IF($A790, IF(COUNTIF('BO Output'!$A$3:$A$2002,$A79),VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE),"")) "andy" wrote: We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help Vlook up returning N?A-Resolved
Well, thanks for feeding back, Andy.
Pete On Jan 19, 1:25*am, andy wrote: Pete_UK thanks for the help, *mark's a coworker and i have *new spreadsheet to use! *You rock! * -- andy "Pete_UK" wrote: You might have an empty cell in your table which is being found correctly, but Excel returns it as zero rather than "". Hope this helps. Pete On Jan 18, 7:01 pm, Mark wrote: In some cases it is returning <blank, as desired, and in other cases it returns "0". Does anyone know why?? "Roger Govier" wrote: Hi Andy Try =IF($A790, IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"" ,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"") -- Regards Roger Govier "andy" wrote in message ... We are using the following formula and it returns N/A =IF($A790,IF(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,' BO Output'!$A$3:$P$2002,4,FALSE)),"") How do i use isna function with this formula -- andy- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOK up returning #N/A even though value exists in lookup array | Excel Worksheet Functions | |||
| VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
| VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
| Vlook | Excel Worksheet Functions | |||
| VLOOK | Excel Worksheet Functions | |||
![andy[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
