CODEWITHSUNDEEP
excelindexingmatchpartial
Jon Smith Johansen
Jon Smith Johansen

Reputation: 1

Using Index match with wildcards to find a partial match

I have seen this question asked before, but I could not understand the answer. I was hoping someone could help me with an Index match formula. with a partial match.

I use this formula :

=(IFERROR(INDEX($A200$:$a$250,MATCH($A1,$B$200:$B$250,0)),""))

To find a exact match and it works very well. exampel :

I want to find some data that is in an collection of data and put it next to the name on a premade sheet.

Now my problem : In this other sheet I want to do the same, but the names have a numbers next to them like this : "John Smith 12345" There is always 5 numbers.

The numbers are in the same cell, I could make it work with making another row of names but then I would have to maintain it as well.

I would appreciate any help, thank you.

Upvotes: 0

Views: 1687

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

You could use a wildcard:

 =(IFERROR(INDEX($A$200:$a$250,MATCH($A1&" ?????",$B$200:$B$250,0)),""))

The above will match Whatever is in A1, followed by a space, followed by any 5 characters

Upvotes: 1

Related Questions