Wildcards in a Index(Match()) formula

[ah2227]

Hi all,

i am working on a spreadsheet with one Sheet of raw data and a second sheet that i would like to have search the raw data, find a first name last name match and then return whatever is in a corresponding cell. i orginally used a Vlookup which worked unless there were any of the same names.

I then tired the following as an array formula - =INDEX('360 Mortgages '!$D$2:$R$2100,MATCH(1,('360 Mortgages '!$D$2:$D$2100='New (3)'!B62)*('360 Mortgages '!$E$2:$E$2100='New (3)'!C62),0),3) which worked, but again only if the names were exact matches. unfortunately some of the names are stored as Name1/Name2. i tried to enter a wildcard function to allow for this as the following - =INDEX('360 Mortgages '!$D$2:$R$2100,MATCH(1,('360 Mortgages '!$D$2:$D$2100='New (3)'!B62&"*")*('360 Mortgages '!$E$2:$E$2100='New (3)'!C62&"*"),0),3) but i keep getting an #N/A result.

My sheets look as follows
The sheet i want to populate is the following. i want the deal end date column to search the raw data sheet for a first name match, surname match and then populate the deal end date if there is on

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]First name[/TD]
[TD]second name[/TD]
[TD]deal end date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]
andrew[/TD]
[TD]smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]james
[/TD]
[TD]jones[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]sarah[/TD]
[TD]wilkes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]daniel[/TD]
[TD]smith[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Raw data sheer, column titles are the same
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]andrew/lucy[/TD]
[TD]smith/smith[/TD]
[TD]01/05/2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]james[/TD]
[TD]jones[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]daniel[/TD]
[TD]smith[/TD]
[TD]08/08/2018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]sarah/michel[/TD]
[TD]wilkes/wood[/TD]
[TD]02/06/2017[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]gary/laura[/TD]
[TD]thompson/scott[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is a simplified version of my workbook however i should be able to apply any answers given

 

[Jonmo1]

Try

=INDEX('360 Mortgages '!$D$2:$R$2100,MATCH(1,(LEFT('360 Mortgages '!$D$2:$D$2100,LEN('New (3)'!B62))='New (3)'!B62)*(LEFT('360 Mortgages '!$E$2:$E$2100,LEN('New (3)'!C62))='New (3)'!C62),0),3)

 

[ah2227]

Thanks for the reply.

I have given it a go and there are some cells where the first name being searched isn't necessarily the first name that appears in the raw data cell, so for example we could be search james smith and the raw date would show alice\james. is there anything that can work in this instance?

Thanks again for the reply