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