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
<tbody>
</tbody>
Raw data sheer, column titles are the same
<tbody>
</tbody>
This is a simplified version of my workbook however i should be able to apply any answers given
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
a | b | c | |
1 | First name | second name | deal end date |
2 | andrew | smith | |
3 | james | jones | |
4 | sarah | wilkes | |
5 | daniel | smith |
<tbody>
</tbody>
Raw data sheer, column titles are the same
a | b | c | |
1 | andrew/lucy | smith/smith | 01/05/2016 |
2 | james | jones | |
3 | daniel | smith | 08/08/2018 |
4 | sarah/michel | wilkes/wood | 02/06/2017 |
5 | gary/laura | thompson/scott |
<tbody>
</tbody>
This is a simplified version of my workbook however i should be able to apply any answers given