Index Match not matching

[Dugzilla]

2 tabels:

Table A, column B has partial names:

Column B
D. Miller
M. Smith
H. Jones

Table B, column B has the list of names I am indexing:

Derelect Miller
Peter Gonads
Mike Smith
Bill Bomba
Hairy Jones
Jimmy Nutball

This is my formula in Table A, C1... using a wildcard:

(From Table A)
=INDEX('TableB!'!$B$1:$B$6,MATCH("*"&B1,$B$1:$B$33,0))

The formula returns all names of Table B just as you would find in the array B1:B6 above.

I tried XLOOKUP for my first time, but it returns a #value error. I suppose because I read where the arrays must be the same size? (XLOOKUP works great when the arrays are equal size.)

I've been working on this for over an hour but can't figure out what is wrong with the formula.

Thank you for any help.

 

[SanjayGMusafir]

I tried XLOOKUP for my first time, but it returns a #value error

XLOOKUP can be a good option for you in current scenario... Use Wildcard as option in search type.

If it still gives error share the formula you are using... Would try to help... Working on formula with non present data

 

[Dugzilla]

XLOOKUP can be a good option for you in current scenario... Use Wildcard as option in search type.

To get XLOOKUP to work, I have to ensure the arrays are the same number of rows. (The first array is only 325 rows, the second array is 943 rows.) If I make the first array 943 rows (B3:B943 for example) to match the second array, the #VALUE is gone. However, it still returns exactly as the INDEX MATCh formula above.

 

[AhoyNC]

Maybe something like this:

Book1
	A	B	C	D	E
1	List 1		List 2		List 1 in List 2
2	D. Miller		Derelect Miller		Derelect Miller
3	M. Smith		Peter Gonads		Mike Smith
4	H. Jones		Mike Smith		Hairy Jones
5			Bill Bomba
6			Hairy Jones
7			Jimmy Nutball
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=IFERROR(INDEX($C$2:$C$7,MATCH(TEXTAFTER(A2," "),TEXTAFTER($C$2:$C$7," "),0)),"")

 

[Dugzilla]

Excellent!!!! You are the man! Thank you.

 

[AhoyNC]

You're welcome. Thanks for the feedback. There could be an issue if more than 1 customer has the last name. If there were 2 Smith then it will only find the first one.

 

[Dugzilla]

You are correct. I will have to work out the formula filtering a criteria called position. Thank you again.

 

[AhoyNC]

If your version of Excel has the FILTER function. Try

Book1
	A	B	C	D	E
1	List 1		List 2		List 1 in List 2
2	D. Miller		Derelect Miller		Derelect Miller
3	M. Smith		Peter Gonads		Mike Smith
4	H. Jones		Mike Smith		Hairy Smith
5	H. Smith		Bill Bomba
6			Hairy Smith
7			Jimmy Nutball
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=FILTER($C$2:$C$7,COUNTIF(A2:A5,LEFT(C2:C7,1)&". "&TEXTAFTER($C$2:$C$7," "))>0)
Dynamic array formulas.

 

[Dugzilla]

If your version of Excel has the FILTER function. Try

Book1
	A	B	C	D	E
1	List 1		List 2		List 1 in List 2
2	D. Miller		Derelect Miller		Derelect Miller
3	M. Smith		Peter Gonads		Mike Smith
4	H. Jones		Mike Smith		Hairy Smith
5	H. Smith		Bill Bomba
6			Hairy Smith
7			Jimmy Nutball
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=FILTER($C$2:$C$7,COUNTIF(A2:A5,LEFT(C2:C7,1)&". "&TEXTAFTER($C$2:$C$7," "))>0)
Dynamic array formulas.

Thank you again. I will plug that in and make sure "I" don't make any mistakes!!!