Wildcard lookup function

[CIBS]

Hi

I am currently using Excel 2007 & 2003

My question is how do you do a wildcard vlookup
let me sure you what I want

Table 1
A B C
Row Part Description

1 YY67HH Screw
2 UY6900 Table Leg
3 KJ78HB Carpet

Table 2
A B C
Row Part Description
1 HH67TK =vlookup(B1,'[Table1]'$B:$C, 2, false) (This should come back with "SCREW").

I know the Vlookup function itself will not work but what else do I need to do to make it look for similarities.

Yes I do know the "'[Table1]' is slight wrong, I can reactify this it's just I do not know how to turn a vlookup into a wildcard look up

KInd Regards

Stephen
2 U890HL

 

[AnotherHiggins]

Whoa, whoa, whoa. You haven't given us nearly enough information to work with.

WHY should that come back with "SCREW"??

Based on the "67" in the middle? That is only one specific example and cannot be extrapolated to the rest of your data.

What is the logic behind how you are matching?
[tab]- Are you always looking to match on the 3rd & 4th characters?
[tab]- Or all numeric characters?
[tab]- What if another part is TK1678K? Will that ever happen?

As for the wildcard, yes, you can use a wildcard with a VLookup.
[tab][COLOR=blue white]=VLOOKUP([red]"*" &[/red] [green]B1[/green] [red]& "*"[/red], Table1!$B:$C, 2, 0)[/color]
You'll want to replace [green]B1[/green] with somethiing to only look at particular characters. But, as I said, you haven't told us how to determine what those characters are yet.

-> I do know the "'[Table1]' is slight wrong

The easiest way to ensure that the syntax is correct for ranges is to just navigate to the proper sheet, then select the desired range. Excel will handle any necessary brackets and apostrophes for you. Well, depending on your point of view, it might be even easier to use Named Ranges. But I never type out references - particularly off-sheet references.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

Much more about wildcards here: thread68-1253790

Agree with John

The easiest way to ensure that the syntax is correct for ranges is to just navigate to the proper sheet, then select the desired range.

also about named ranges being even easier.

If using named ranges rather than navigating to the range use Insert, Name, Paste - avoids any potential mispelling.

But I never type out references - particularly off-sheet references.

If the lookup range is a named range (say "MyRangeName") in another workbook one trick is to temporarily copy the worksheet to the current workbook. Create the lookup using Insert, Name, Paste as above. Then copy the sheet back to the file it came from originally.
Your workbook now has a named range that points to the range in the external workbook. Your Formula just looks like this:
=vlookup(b2,MyRangeName,5,0) rather than having a complicated range definition including file path.



Gavin

 

[CIBS]

Hi

It will need to look numeric numbers but these can be any where it just needs to get the nearest match to whats in the reference cell

Regards

Stephen

 

[Gavona]

Clear as mud. How about answering John's questions? And giving us a sample of your table.

get the nearest match

You will want the final argument to be 1 (true) not 0 (false) then and your lookup table must be sorted.
And if you really mean 'nearest' that may be problematic as well. Excel will work down your lookup table until it finds a value greater than that you were looking up. It will then return based on the previous row. Sounds complicated? Try it and see with a simple table.

Gavin