VLOOKUP

[Guest]

i trying to use this forumla:
=VLOOKUP

if unable to find out the values, can the data cells auto set to blank?

 

[Dave Peterson]

You can't make the cells empty, but you can make them look blank.

=if(isna(vlookup(...)),"",vlookup(...))
or in xl2007
=iferror(vlookup(...),"")

 

[Guest]

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]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

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?

 

[Guest]

I wan do a 2nd check but if result not found, the searchResult should return
empty.

 

[Pete_UK]

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

 

[Dave Peterson]

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.

 

[Guest]

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?

 

[Guest]

In short i mean if search is unable to find, the cells should set to
empty/clear.

 

[Dave Peterson]

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.

 

[Guest]

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..

 

[Peo Sjoblom]

=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)

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..

 

[Guest]

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.

 

[Dave Peterson]

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.

 

[Guest]

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]

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.