Vlookup with partial match

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
You can do a partial match using wildcards.

The asterisk represents the same thing that it did in the good 'ol days of DOS. Similarly, both DOS and Excel will treat the question mark (?) as a wildcard that represents exactly one character.

In A1:A10, put a list of names (make one of those names Michael).

In cell C1 put =VLOOKUP(B1,A1:A10,1,FALSE), where B1 is the lookup value.

To lookup Michael, in B1 put "Mi*" (without the apostrophes).

An alternative formula is:

=INDEX(A1:A10,MATCH(B1,A1:A10,0))

Regards,

Mike
 
Upvote 0

skywaz

New Member
Joined
Sep 6, 2002
Messages
21
FYI Spockster, Mike's advice on INDEX MATCH is a good alternative to VLOOKUP. It's much faster on larger spreadsheets.

=INDEX(A1:A10,MATCH(B1,A1:A10,0))

Darin
 
Upvote 0

Spockster

Board Regular
Joined
Jun 9, 2002
Messages
108
Thanks for the help.

I have one more question? If i want to look up info in more than one cell, for instants one cell has last name and another cell has first name. How do i do the match using these two cells?

Thanks once again for all your help
 
Upvote 0

playaller

New Member
Joined
Oct 2, 2013
Messages
34
Example of formula:
List of names in column A. First Name is Column A1 and Last Name in Column B1. Paste formula in C1 and drag down. Details to find in columns range D through F. Result to show in column 5 (column E)


2 Cells
Code:
=IFERROR(VLOOKUP(A1&" "&B1,D:F,5,FALSE),"")

2 Cells with Wild Card
Code:
=IFERROR(VLOOKUP(A1&"*"&" "&B1&"*",D:F,5,FALSE),"")

1 Cell
Code:
=IFERROR(VLOOKUP(A1,D:F,5,FALSE),"")

1 Cell with Wild Card After Name
Code:
=IFERROR(VLOOKUP(A1&"*",D:F,5,FALSE),"")

1 Cell with Wild Card Before and After Name
Code:
=IFERROR(VLOOKUP("*"&A1&"*",D:F,5,FALSE),"")
 
Upvote 0

Forum statistics

Threads
1,187,172
Messages
5,962,029
Members
438,575
Latest member
aleksa02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top