# Vlookup with partial match

#### Spockster

##### Board Regular
How do you do a vlookup with a partial match?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Ekim

##### Well-known Member
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

• LGFN1

#### skywaz

##### New Member
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

• RAM1972 and LGFN1

#### Spockster

##### Board Regular
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

#### playaller

##### New Member
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),"")``