Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the
formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
=if(isNA(Vllookup()),"",Vllookup())
"Finance Guru" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Can someone please advise me ; if VLOOKUP( ) doesn't find the result
in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks I wrote my own UDF: Function NewVLookup(Value As Variant, Table As Variant, _ ColIndex As Integer, RangeLookup As Boolean) As Variant With Application If .IsNA(.VLookup(Value, Table, _ ColIndex, RangeLookup)) Then NewVLookup = 0 '<================ change 0 to "" if you want blank instead of 0 Else NewVLookup = .VLookup(Value, Table, ColIndex, RangeLookup) End If End With End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Hi Joel - Thanks for a FAST response.
I don't quite undestand the the reason for the second Vllookup() , or what should go into the quotes. My VLOOKUP() looks like this - VLOOKUP(A1,sheet4!A1:c300,2,0). Am i not able to adapt this in some way,to display a blank cell wher the criteria is not met. I hope you can help. Thanks again "Joel" wrote: =if(isNA(Vllookup()),"",Vllookup()) "Finance Guru" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Hi Ian - Thanks for replying,but i think this way ahead of me,I am not a
programmer. I wouldn't even begin to know where to start. Thanks anyway FinanceGuru "IanKR" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks I wrote my own UDF: Function NewVLookup(Value As Variant, Table As Variant, _ ColIndex As Integer, RangeLookup As Boolean) As Variant With Application If .IsNA(.VLookup(Value, Table, _ ColIndex, RangeLookup)) Then NewVLookup = 0 '<================ change 0 to "" if you want blank instead of 0 Else NewVLookup = .VLookup(Value, Table, ColIndex, RangeLookup) End If End With End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
if(ISNA(VLOOKUP(A1,sheet4!A1:c300,2,0)),"",VLOOKUP (A1,sheet4!A1:c300,2,0))
the first VLOKKUP is used to test if you get a NA. It is inside the ISNA(). The "" is if it does find the NA then print nothing. The second VLOOKUP returns the value when there isn't a NA. "Finance Guru" wrote: Hi Joel - Thanks for a FAST response. I don't quite undestand the the reason for the second Vllookup() , or what should go into the quotes. My VLOOKUP() looks like this - VLOOKUP(A1,sheet4!A1:c300,2,0). Am i not able to adapt this in some way,to display a blank cell wher the criteria is not met. I hope you can help. Thanks again "Joel" wrote: =if(isNA(Vllookup()),"",Vllookup()) "Finance Guru" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Way to go Joel. Thankyou very much for your assistance.
Youv've made it sound so simple,and it works "You've just lightened a heavy load!" Kind regards FG "Joel" wrote: if(ISNA(VLOOKUP(A1,sheet4!A1:c300,2,0)),"",VLOOKUP (A1,sheet4!A1:c300,2,0)) the first VLOKKUP is used to test if you get a NA. It is inside the ISNA(). The "" is if it does find the NA then print nothing. The second VLOOKUP returns the value when there isn't a NA. "Finance Guru" wrote: Hi Joel - Thanks for a FAST response. I don't quite undestand the the reason for the second Vllookup() , or what should go into the quotes. My VLOOKUP() looks like this - VLOOKUP(A1,sheet4!A1:c300,2,0). Am i not able to adapt this in some way,to display a blank cell wher the criteria is not met. I hope you can help. Thanks again "Joel" wrote: =if(isNA(Vllookup()),"",Vllookup()) "Finance Guru" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
The method Joel advocates is the best approach in 2003 and earlier, but
suffers from the problem that most of the time, VLOOKUP must be called twice, first to test for N/A and again to get the data to return to the cell. In a large workbook with lots of VLOOKUPs, this will cause a performance hit. In Excel 2007, you can use the IFERROR function, e.g., =IFERROR(VLOOKUP(...),"Value If Error") This uses only a single VLOOKUP, but works only in Excel 2007. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Finance Guru" wrote in message ... Way to go Joel. Thankyou very much for your assistance. Youv've made it sound so simple,and it works "You've just lightened a heavy load!" Kind regards FG "Joel" wrote: if(ISNA(VLOOKUP(A1,sheet4!A1:c300,2,0)),"",VLOOKUP (A1,sheet4!A1:c300,2,0)) the first VLOKKUP is used to test if you get a NA. It is inside the ISNA(). The "" is if it does find the NA then print nothing. The second VLOOKUP returns the value when there isn't a NA. "Finance Guru" wrote: Hi Joel - Thanks for a FAST response. I don't quite undestand the the reason for the second Vllookup() , or what should go into the quotes. My VLOOKUP() looks like this - VLOOKUP(A1,sheet4!A1:c300,2,0). Am i not able to adapt this in some way,to display a blank cell wher the criteria is not met. I hope you can help. Thanks again "Joel" wrote: =if(isNA(Vllookup()),"",Vllookup()) "Finance Guru" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Sorry I should have mentioned that I am on Excel 2003. I will remember to
quote the version number I am using on any future questions. Thanks for taking the time to respond. FG "Chip Pearson" wrote: The method Joel advocates is the best approach in 2003 and earlier, but suffers from the problem that most of the time, VLOOKUP must be called twice, first to test for N/A and again to get the data to return to the cell. In a large workbook with lots of VLOOKUPs, this will cause a performance hit. In Excel 2007, you can use the IFERROR function, e.g., =IFERROR(VLOOKUP(...),"Value If Error") This uses only a single VLOOKUP, but works only in Excel 2007. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Finance Guru" wrote in message ... Way to go Joel. Thankyou very much for your assistance. Youv've made it sound so simple,and it works "You've just lightened a heavy load!" Kind regards FG "Joel" wrote: if(ISNA(VLOOKUP(A1,sheet4!A1:c300,2,0)),"",VLOOKUP (A1,sheet4!A1:c300,2,0)) the first VLOKKUP is used to test if you get a NA. It is inside the ISNA(). The "" is if it does find the NA then print nothing. The second VLOOKUP returns the value when there isn't a NA. "Finance Guru" wrote: Hi Joel - Thanks for a FAST response. I don't quite undestand the the reason for the second Vllookup() , or what should go into the quotes. My VLOOKUP() looks like this - VLOOKUP(A1,sheet4!A1:c300,2,0). Am i not able to adapt this in some way,to display a blank cell wher the criteria is not met. I hope you can help. Thanks again "Joel" wrote: =if(isNA(Vllookup()),"",Vllookup()) "Finance Guru" wrote: Can someone please advise me ; if VLOOKUP( ) doesn't find the result in the formula,the the cell shows #NA. How can I get a cell to be blank,if the value isn't found. I have only just started using VLOOKUP & HLOOKUP,so any help would be greatfully accepted. Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
Hi Ian - Thanks for replying,but i think this way ahead of me,I am
not a programmer. I wouldn't even begin to know where to start. Thanks anyway FinanceGuru Just copy the code I posted into a normal code module. I assumed that because you posted this query in the ms....programming NG, you were after a VBA solution. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I prevent #NA in Lookup
I tried this formula, but it did not work for me. Instead of a blank, the
number returned was the closest number in the array (as if TRUE was used in the vlookup). Do you have a suggestion? Here is my forumula =IF(ISNA(VLOOKUP(W7,'[INC STMTS.xls]detail for input'!$A$2:$C$4979,2,0)),"",VLOOKUP(W7,'[INC STMTS.xls]detail for input'!$A$2:$C$4979,2,0)) Why is there a 0 instead of FALSE at the end of the vlookup? Thanks for helping, Sue "Joel" wrote: if(ISNA(VLOOKUP(A1,sheet4!A1:c300,2,0)),"",VLOOKUP (A1,sheet4!A1:c300,2,0)) the first VLOKKUP is used to test if you get a NA. It is inside the ISNA(). The "" is if it does find the NA then print nothing. The second VLOOKUP returns the value when there isn't a NA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
HOW CAN I PREVENT EXCEL FILES FROM BEING DELETED OR PREVENT TRASH | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |