vlookup for searching?

[SBuzzT]

I am using the vlookup function with wildcards to search column A to find the adjacent value in column B. This function works, but I need to expand it to return all the matches (for partial searches). Additionally, I would also like to include the value from column A in the same cell the result(s) are returned (for side by side comparison).

Any ideas how I could do this without using any macros?

So, the function is in C2 and the search term is in C1
=VLOOKUP(C1&"*",$A$2:$B$42,2,FALSE)

 

[SkipVought]

Hi,

I need to expand it to return all the matches (for partial searches).

Would you care to elaborate on what that actually means. Remember, on one can see your sheet or whats on your mind. You must explain.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

When I enter in a word or part of the word, I want to return ALL the matches or partial matches (like a search) - not just the first one.

 

[SkipVought]

Check out the Find All feature in Edit > Find

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

So with these columns, if my search text was "bl"

red 1
blue 2
green 3
white 4
black 5

it would return

blue 2
black 5

 

[SBuzzT]

I know how to do that... I need the values in the adjacent columns.

 

[SkipVought]

HOW do you want these values presented to you? A function returns a STRING (one value)?

How does the AutoFilter work for you with the CONTAINS criteria?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

I wanted them returned in cells as illustrated above. I thought of some sort of array, but don't know how to set it up.

 

[SkipVought]

faq68-5829.

You could return EXACTLY what you want.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

With the formula the way it is, I will get:

blue

I need:

blue 2
black 5

 

[SkipVought]

You might also consider the Advanced Filter and use Copy to another location option.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

I am not sure what you are trying to tell me. Could you elaborate?

 

[SBuzzT]

I need this to work with someone just typing in a few letters into a specific cell. The terms will be on a different sheet eventually. This basically will be a translator matching the search string to the corresponding terms in another language.

 

[SkipVought]

it would be difficult to do with a formula, because you could return ONE or ONE HUNDRED, you get what I mean.

A query would return exactly what you need. The only caveat is that you must use VBA code, as parameters cannot use wildcard features.

You would need to add a row of headings to your data and have it on a separate sheet (table).

Then, supposing that your data were on Sheet1, your query might look something like this...

s =     "Select Color, Val "
s = s & "From Sheet1$ "
s = s & "Where Color Like '" & Sheet2![C1] & "*'"

where Color and Val are your 2 column headings on Sheet1 and your data criteria is input on Sheet2.

Could be returned to Sheet2 also.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

I thought of a query, but I really have to avoid any kind of macro.

 

[SkipVought]

So what do you need to do with the results?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SBuzzT]

just display them each in a separate cell.

 

[SkipVought]

Don't know that there's any practical way to do this without VBA code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Kurupt55]

I managed to do it with a combination of functions
dont have time to post a full solution but start with
=IF(ISERROR(FIND($A$1,$B1,1)),0,$B1)
Assuming your search text is in cell A1 and your data is in B1, then fill this to the bottom of your data
This will filter out entries that do not contain your search criteria.
The only problem is that it IS case sensitive so search for Bl will return Black and Blue but bl will not
But as usual skip is right and VBA will be much tidier and easier

Impossible is Nothing

 

[Loomah]

substitute FIND with SEARCH to eliminate the case sensitive issue.

bottom line though is that skip is still right!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​