Index Match Wild Card

[hshone]

Hi all,

I'm trying to have an index and match formula which wild cards a last name against the date they attended.

At the moment I have this formula:

=INDEX([Date Attended],MATCH(1,("*"&[Last Name]&"*"=[Full Name)*([Date]=[Date Attended]),0))

I have entered with the CRTL + SHFT+ ENTR but it still not working, any ideas?

 

[Jonmo1]

Try

=INDEX([Date Attended],MATCH(1,(ISNUMBER(SEARCH([Last Name],[Full Name)))*([Date]=[Date Attended]),0))

 

[hshone]

Worked like a treat, many thanks!!!

Try

=INDEX([Date Attended],MATCH(1,(ISNUMBER(SEARCH([Last Name],[Full Name)))*([Date]=[Date Attended]),0))

 

[Jonmo1]

You're welcome.

 

[hshone]

Hi,

It doesn't seem to work when there are multiple 'line items' i.e when i have multiple people with the surname smith.

 

[Jonmo1]

Can you define "doesn't seem to work" ?
What exactly happens?
Do you get an error? What error?
Do you get the wrong result? What result DO you get, how is that different from the result you were expecting?