| 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
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 |
|
#3
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 |
|
#4
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 |
|
#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
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 - |
|
#8
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 - |
|
#9
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 - |
|
#10
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 - |
|
#11
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 |
|
#12
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 |
|
#13
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 |
| 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)
Hybrid Mode
