VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i trying to use this forumla:
=VLOOKUP

if unable to find out the values, can the data cells auto set to blank?
 
You can't make the cells empty, but you can make them look blank.

=if(isna(vlookup(...)),"",vlookup(...))
or in xl2007
=iferror(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.
 
I don't understand the comment.
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.
Click to expand...
 
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.
 
Maybe:

=if(isna(vlookup(...)),"Invalid data entered",vlookup(...))

Is this for a macro/VBA or a formula in a worksheet?


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.
Click to expand...
 
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?
 
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)))


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?
Click to expand...
 
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
 
Sometimes, I like to use not(isna(

=if(not(isna(vlookup(first))),vlookup(first),
if(not(isna(vlookup(second))),vlookup(second),"")


I wan do a 2nd check but if result not found, the searchResult should return
empty.
Click to expand...
 
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?
 
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.


In short i mean if search is unable to find, the cells should set to
empty/clear.
Click to expand...
 
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..
 
=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 said:
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..
Click to expand...
 
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.
 
I don't understand.


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.
Click to expand...
 
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!
 
Put this in B13

=if(isna(vlookup(b18,yourlookuprange,yourcolumn,false)),""
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.
 
Back
Top