| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP
i trying to use this forumla:
=VLOOKUP if unable to find out the values, can the data cells auto set to blank? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
You can't make the cells empty, but you can make them look blank.
=if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
currently if i search a valid data, it will shows the result.
But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
I don't understand the comment.
kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
i mean when i have this:
MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Maybe:
=if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Hi Dave,
i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
So you want to check a couple of different tables?
If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
I wan do a 2nd check but if result not found, the searchResult should return
empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
The general form would be:
=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not present",vlookup_2,vlookup_1)) where vlookup_1 is your first VLOOKUP formula and vlookup_2 your second. You can make the message "" if you want it to appear empty with no matches. Hope this helps. Pete On Jul 5, 6:50 pm, kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlooku*p(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Sometimes, I like to use not(isna(
=if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Hi Both,
I dont have vlookup_2. I only using one VLOOKUP. =VLOOKUP(A8,Data!AI:AL,2,FALSE) So i combine into : =IF(ISNA(A8,Data!AI:AL,2,FALSE),IF(ISNA(vlookup_2) ,"notpresent",vlookup_2,A8,Data!AI:AL,2,FALSE)) I remove vlookup_2? "Pete_UK" wrote: The general form would be: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),"not present",vlookup_2,vlookup_1)) where vlookup_1 is your first VLOOKUP formula and vlookup_2 your second. You can make the message "" if you want it to appear empty with no matches. Hope this helps. Pete On Jul 5, 6:50 pm, kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlooku-p(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
In short i mean if search is unable to find, the cells should set to
empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
In that previous suggestion, do the following:
Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Hi Dave,
Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
=IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0))
will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
My search field is at B18.
And search results will be at B13,B14,B15 So if there is valid return result, it will show on B13, B14. And user have to re-enter the searching at B18, and if invalid, it will return blank at B13, B14. "Peo Sjoblom" wrote: =IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0)) will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
I don't understand.
kyoshirou wrote: My search field is at B18. And search results will be at B13,B14,B15 So if there is valid return result, it will show on B13, B14. And user have to re-enter the searching at B18, and if invalid, it will return blank at B13, B14. "Peo Sjoblom" wrote: =IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0)) will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
I have a search engine for user to enter a search at B18.
And search results will shown on B13,B14,B15 I mean if i enter apple in B18. if apple is inside my data sheet, B13 will reflect apple. So the user enter another search at B18. Example, orange. But if orange is not in data sheet. How do i make B13 reflect empty or blank? So as to indicate orange has not been found. Thanks! "Dave Peterson" wrote: I don't understand. kyoshirou wrote: My search field is at B18. And search results will be at B13,B14,B15 So if there is valid return result, it will show on B13, B14. And user have to re-enter the searching at B18, and if invalid, it will return blank at B13, B14. "Peo Sjoblom" wrote: =IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0)) will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
Put this in B13
=if(isna(vlookup(b18,yourlookuprange,yourcolumn,fa lse)),"" vlookup(b18,yourlookuprange,yourcolumn,false)) I still don't understand what you're doing with B14 and B15. You explained that you typed something to search for in B18. You wanted the found results or "" in B13. kyoshirou wrote: I have a search engine for user to enter a search at B18. And search results will shown on B13,B14,B15 I mean if i enter apple in B18. if apple is inside my data sheet, B13 will reflect apple. So the user enter another search at B18. Example, orange. But if orange is not in data sheet. How do i make B13 reflect empty or blank? So as to indicate orange has not been found. Thanks! "Dave Peterson" wrote: I don't understand. kyoshirou wrote: My search field is at B18. And search results will be at B13,B14,B15 So if there is valid return result, it will show on B13, B14. And user have to re-enter the searching at B18, and if invalid, it will return blank at B13, B14. "Peo Sjoblom" wrote: =IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0)) will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
cant.
My B14 & B15 is for same results (those duplicate). You can ignore them. The problem is how to make the search results fields back to empty or blank if search is unable to find for the 2nd time. "Dave Peterson" wrote: Put this in B13 =if(isna(vlookup(b18,yourlookuprange,yourcolumn,fa lse)),"" vlookup(b18,yourlookuprange,yourcolumn,false)) I still don't understand what you're doing with B14 and B15. You explained that you typed something to search for in B18. You wanted the found results or "" in B13. kyoshirou wrote: I have a search engine for user to enter a search at B18. And search results will shown on B13,B14,B15 I mean if i enter apple in B18. if apple is inside my data sheet, B13 will reflect apple. So the user enter another search at B18. Example, orange. But if orange is not in data sheet. How do i make B13 reflect empty or blank? So as to indicate orange has not been found. Thanks! "Dave Peterson" wrote: I don't understand. kyoshirou wrote: My search field is at B18. And search results will be at B13,B14,B15 So if there is valid return result, it will show on B13, B14. And user have to re-enter the searching at B18, and if invalid, it will return blank at B13, B14. "Peo Sjoblom" wrote: =IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0)) will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#22
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP
As far as I understand this, you've gotten several responses that do just that.
I don't know what else to offer. kyoshirou wrote: cant. My B14 & B15 is for same results (those duplicate). You can ignore them. The problem is how to make the search results fields back to empty or blank if search is unable to find for the 2nd time. "Dave Peterson" wrote: Put this in B13 =if(isna(vlookup(b18,yourlookuprange,yourcolumn,fa lse)),"" vlookup(b18,yourlookuprange,yourcolumn,false)) I still don't understand what you're doing with B14 and B15. You explained that you typed something to search for in B18. You wanted the found results or "" in B13. kyoshirou wrote: I have a search engine for user to enter a search at B18. And search results will shown on B13,B14,B15 I mean if i enter apple in B18. if apple is inside my data sheet, B13 will reflect apple. So the user enter another search at B18. Example, orange. But if orange is not in data sheet. How do i make B13 reflect empty or blank? So as to indicate orange has not been found. Thanks! "Dave Peterson" wrote: I don't understand. kyoshirou wrote: My search field is at B18. And search results will be at B13,B14,B15 So if there is valid return result, it will show on B13, B14. And user have to re-enter the searching at B18, and if invalid, it will return blank at B13, B14. "Peo Sjoblom" wrote: =IF(ISNA(VLOOKUP(A8,Data!AI:AL,2,0)),"",VLOOKUP(A8 ,Data!AI:AL,2,0)) will return blank when there is no match (cannot be empty since it holds a formula) -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "kyoshirou" wrote in message ... Hi Dave, Why do i need to have 2 vlookup? i only need to retrieve the data from 1 database sheet. I mean if i search it will returns the output. And then another search (invalid data), the output will be empty. I think my understanding is kind of mess.. Sry.. "Dave Peterson" wrote: In that previous suggestion, do the following: Replace vlookup(first) with your first vlookup formula. Replace vlookup(second) with your second vlookup formula. You'll have four total replacements to do. kyoshirou wrote: In short i mean if search is unable to find, the cells should set to empty/clear. "Dave Peterson" wrote: Sometimes, I like to use not(isna( =if(not(isna(vlookup(first))),vlookup(first), if(not(isna(vlookup(second))),vlookup(second),"") kyoshirou wrote: I wan do a 2nd check but if result not found, the searchResult should return empty. "Dave Peterson" wrote: So you want to check a couple of different tables? If the match isn't found in the first table, then check the second table? =if(not(isna(vlookup(firsttableformula))),vlookup( firsttableformula),vlookup(secondtableformula))) kyoshirou wrote: Hi Dave, i trying that on formula in a worksheet. I get what you trying to help me. It works when i seach a valid or invalid data in the search cell. But is it possible if i make another search inside the cell, if the data cannnot be found, it will auto clear the first search results. Coz right now, the old serch result will be still there. Do i need to use marco? "Dave Peterson" wrote: Maybe: =if(isna(vlookup(...)),"Invalid data entered",vlookup(...)) Is this for a macro/VBA or a formula in a worksheet? kyoshirou wrote: i mean when i have this: MsgBox "Invalid Data Entered" it will gives user a error msg when invalid search is entered. Can that MsgBox "Invalid Data Entered" be used inside VLOOKUP togther? Coz right now, i do no use any "Search" button to activate the code. "Dave Peterson" wrote: I don't understand the comment. kyoshirou wrote: currently if i search a valid data, it will shows the result. But if i search again with an invalid data, the cells cant reflect blank or look blank. "Dave Peterson" wrote: You can't make the cells empty, but you can make them look blank. =if(isna(vlookup(...)),"",vlookup(...)) or in xl2007 =iferror(vlookup(...),"") kyoshirou wrote: i trying to use this forumla: =VLOOKUP if unable to find out the values, can the data cells auto set to blank? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
