INDEX/MATCH with wildcard

[LauraBAP]

So, I have a list of employee names that I'm matching a plan type (there are multiple plans per employee) and having it return a value.

My issue is that the value from the sheet that the information is on will sometimes have a space at the end of the employee name. I've been trying to use a wildcard with my formula, but I keep getting N/A.

I know I have to be doing something wrong, but I'm getting frustrated.

C4 = Employee name
D4 = plan type

=INDEX('Membership Details'!$C$3:$K$1599,MATCH(1,('Membership Details'!$C$3:$C$1599=C4&"*")*('Membership Details'!$G$3:$G$1599=D4),0),9)

I know the error is in the ('Membership Details'!$C$3:$C$1599=C4&"*")*('Membership Details'!$G$3:$G$1599=D4) part, I'm just not smart enough (or patient enough at this point) to figure it out.

Any help is SO appreciated!

Laura

 

[ExcelElliott]

Apologies if this is a stupid question, but are you entering the formula by using Ctrl + Shift + Enter?

 

[LauraBAP]

Not a stupid question at all lol - I am. I thought about editing earlier to include that little tid bit of info.

 

[Marcelo Branco]

My issue is that the value from the sheet that the information is on will sometimes have a space at the end of the employee name. I've been trying to use a wildcard with my formula, but I keep getting N/A.

Maybe this...
=INDEX('Membership Details'!$C$3:$K$1599,MATCH(1,(TRIM('Membership Details'!$C$3:$C$1599)=C4)*('Membership Details'!$G$3:$G$1599=D4),0),9)
Ctrl+Shift+Enter

Hope this helps.

M.

 

[LauraBAP]

Nope. I'm still getting the N/A error.

The info in C4 doesn't have the space at the end of the name, it's the information on the Membership Details $C3:$C$1599 where the space is included. I did do a test on that sheet, and took out the space, and it worked. That information changes every month, and I don't want to have to remove spaces for 1500 lines.

 

[Marcelo Branco]

Nope. I'm still getting the N/A error.

The info in C4 doesn't have the space at the end of the name, it's the information on the Membership Details $C3:$C$1599 where the space is included. I did do a test on that sheet, and took out the space, and it worked. That information changes every month, and I don't want to have to remove spaces for 1500 lines.

In my tests, using TRIM, everything worked perfectly - TRIM deletes extraneous spaces.
The formula should also have worked for you. If it didn't, so your problem is not only extra space(s) at the end.

See if this new version works - array formula
=INDEX('Membership Details'!$C$3:$K$1599,MATCH(1,(ISNUMBER(SEARCH(C4,'Membership Details'!$C$3:$C$1599)))*('Membership Details'!$G$3:$G$1599=D4),0),9)
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[LauraBAP]

You are amazing.

Thank you, THANK YOU!

Laura

 

[Marcelo Branco]

You are welcome. Glad to help

M.