Some sort of Vlookup, sumproduct

[techfreak]

Hi Board,

It's been so long since I've done this I've forgotten how!

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Owner[/TD]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]Name[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]Joe[/TD]
[TD]Bloggs[/TD]
[TD]Joe Joseph Bloggs[/TD]
[TD]ANother[/TD]
[/TR]
</tbody>[/TABLE]

For ease I have put this data on one table, however, the data is split across two tables in two sheets.

So essentially, the blank cell above which needs populating is column A for the owner (shown completed here as Anne based on the data in E), to do this I need to first find a match between the first name and surname in cells B + C against the data in column D, which can contain a middle name.

Once this match has been made it should lookup the owner in E and populate this in A, as a first name only. If necessary I can create another column which translates ANother to Anne, JBloggs to Joe etc.

Can someone please point me in the right direction.

Many thanks

 

[Special-K99]

Re: Some sort of Vlookup, sumproduct, help

Your example data should reflect the data AS IT IS.
Please supply TWO tables so we know exactly what the tables look like and what data should be extracted.

 

[techfreak]

Re: Some sort of Vlookup, sumproduct, help

Thanks for replying, here goes:

Table 1:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Owner[/TD]
[TD]First Name[/TD]
[TD]Surname[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Joe[/TD]
[TD]Bloggs[/TD]
[/TR]
</tbody>[/TABLE]


Table 2:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]Joe Joseph Bloggs[/TD]
[TD]ANother
[/TD]
[/TR]
</tbody>[/TABLE]


A3 in Table 1 is what needs populating based on the data in Table 2 column B, based on the match between B+C in Table 1 and A in Table 2.

The next part I am trying to solve is, if there is no match then it should be N/A.

If there are duplicates entries in table 2 with multiple entries for the same Name in column A then it should highlight this in Table 1 A3 by some sort of conditional formatting to change the colour.

Hope that helps to clear it up?

 

[Special-K99]

Re: Some sort of Vlookup, sumproduct, help

Mm, not sure how you're gonna do a lookup if part of the lookup value is missing.
Will have a think but I probably wont have a solution for this...

 

[Special-K99]

Re: Some sort of Vlookup, sumproduct, help

Two minutes later...

try this

=IFERROR(INDEX(Table2!B1:B1000,MATCH(A1&"*"&B1,Table2!D1:D1000,0),1),"N/A")

 

[techfreak]

Re: Some sort of Vlookup, sumproduct, help

doesn't quite work. All come up as N/A. What is the column D pointing to?

When you say part of the lookup value is missing, what are you referring to? Maybe I haven't explained this properly. Basically in Table 1 - the formula should insert the data it finds in table 2 column B i.e. Owner to Owner. This should be done based on the names matching in table 1 and 2, but the way the names are entered are shown as above i.e. split first and surname in table 1 and all entered in 1 column in table 2.

If it can't find a match between names in both tables then the owner is N/A.

 

[Special-K99]

Re: Some sort of Vlookup, sumproduct, help

Should be

=IFERROR(INDEX(Table2!B1:B100,MATCH(A1&"*"&B1,Table2!A1:A100,0),1),"N/A")

Joe Bloggs is the lookup value "Joseph" is missing from that lookup value.

 

[techfreak]

Re: Some sort of Vlookup, sumproduct, help

Ah ok. Tried it and I get all 0's entered in column A table 1.

On a separate note, is there not a way to match the data in table 2 column A based on some sort of wildcard that finds a match based on 2 out of the 3 values matching? so it essentially ignores the middle name?

 

[Special-K99]

Re: Some sort of Vlookup, sumproduct, help

I dont habve much time to spend on this.

The below works.

Using a blank sheet

Put Joe in B1
Put Bloggs in C1
Put Joe Jospeh Bloggs in E1
Put ANother in F1

in A2
=IFERROR(INDEX(F1:FF100,MATCH(B1&"*"&C1,E1:E10,0),1),"N/A")

returns ANother

amend the formula as per your spreadsheet setup.

 

[techfreak]

Re: Some sort of Vlookup, sumproduct, help

I dont habve much time to spend on this.

The below works.

Using a blank sheet

Put Joe in B1
Put Bloggs in C1
Put Joe Jospeh Bloggs in E1
Put ANother in F1

in A2
=IFERROR(INDEX(F1:FF100,MATCH(B1&"*"&C1,E1:E10,0),1),"N/A")

returns ANother

amend the formula as per your spreadsheet setup.

Thanks for your help Special K. this does the job and has saved me some time manually doing the searches. Many thanks