Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I know how to use a wildcard with Vlookup on the reference value, but what about on the matched column index?

I know you can do for instance VLOOKUP("*Hello*",A4:G4,2,FALSE)

However, what if you wanted to match a cell that is "Hello", with another one that is "Why, Hello there!" (so the opposite i suppose)

My data is set in a way where a macro is going to ask if A4 in workbook 1 matches C2:C25000 in workbook 2 to return the corresponding value in D back to workbook 1. The thought is that when A4 in workbook 1 says Its DHS Here, that I could input a value such as DHS in column C in workbook 2 and have it say its a match.

Is this possible?

share|improve this question

migrated from stackoverflow.com Feb 23 '13 at 15:49

This question came from our site for professional and enthusiast programmers.

    
The vlookup code was supposed to have asterisks before and after 'Hello' - sorry for that - should have wrote it in code. –  Dm3k1 Jan 28 '13 at 23:19

4 Answers 4

The key is to bild the lookup value by concatenating *'s, like this

=VLOOKUP("*"&A4&"*",'[workbook 2.xlsx]Sheet1'!$C$2:$D$25000,2,0)

(adjust sheet name to suit)

share|improve this answer

Since you are already planning on using a macro, what is the possibility of using an add-on? From my understanding, you are looking for fuzzy lookup. Excel created an add-on to accomplish this task found here.

share|improve this answer

I've run into this problem many time. You need put the wildcard phrase in a different cell.

Cell B1 has this in the cell: *Hello*

Your new formula: VLOOKUP(B1,A4:G4,2,FALSE)

It should work now.

share|improve this answer

My understanding is that the "lookup value" could be "Why, Hello There!" or "DHS Here" and you want that to match respectively with "Hello" and "DHS" in the lookup range.

Given that then for a formula approach try the following:

=LOOKUP(2^15,SEARCH(C2:C25000,A4)/(C2:C25000<>""),D2:D25000)

Where A4 is the lookup value, insert workbook/worksheet references as required.

That will find the last "match" in C2:C25000, for first match you can use

=INDEX(D2:D25000,MATCH(1,INDEX(ISNUMBER(SEARCH(C2:C25000,A4))*(C2:C25000<>""),0),0))

Note: either of those could match partial "words" so if A4 is "Rat catcher" that might match with "cat" or "catch" as well as "rat" - you can add in spaces to force the match to be with whole words only, e.g. first formula can be addapted to:

=LOOKUP(2^15,SEARCH(" "&C2:C25000&" "," "&A4&" ")/(C2:C25000<>""),D2:D25000)

although that assumes all words separated by spaces with no punctuation.....

share|improve this answer
    
From @Jonathon: Barry, that is just what I need to lookup room names in a list of abrreviations and return the room type as listed in ventilation codes. I normally think I'm pretty good with excel, but for this example, I'm stumped. Can you explain how it works? Thanks –  Canadian Luke Nov 1 '13 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.