Vlookup value based on a name if in one of the tables the name is split in two cells

[tanuxaxaxa]

Hi guys!

I have come across a problem

I need to vlookup a team based on the name, however in the table where I add vlookup value the whole name is put in one cell, and in the table where I vlookup value in it is split into two cells:

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Name [/TD]
[TD]Team[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10$[/TD]
[TD]Maria D.[/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20$[/TD]
[TD]Stefan J.[/TD]
[TD]?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table 2[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Team[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maria[/TD]
[TD]D.[/TD]
[TD]PR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stefan[/TD]
[TD]J.[/TD]
[TD]RD[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Not sure how to tackle the issue and would appreciate any help!

 

[Joe4]

Insert a column before the First Name field in Table 2, and sew the two values together to build the full name, i.e.
=B2 & " " & C2

Then VLOOKUP will work.

 

[tanuxaxaxa]

Unfortunately, cannot do that (but def though ab that )
The table is used through the organization and is needed to be that way

 

[Joe4]

Unfortunately, cannot do that (but def though ab that )

Not even if you "hide" that column so that no one can see it?
It does not need to be visible for VLOOKUP to work on it.

 

[tanuxaxaxa]

Yeah, but I hoped maybe there is a more elegant solution I just haven't thought of

 

[Joe4]

I hoped maybe there is a more elegant solution

There probably is, but it wouldn't be a VLOOKUP formula. Maybe using an INDEX/MATCH solution?
If I get the chance, I will play around and see if I can come up with something.

 

[Joe4]

OK. I found a way to do it!

Let's say that the data from Table 1 in your example is in cells A4:B5, and the data from Table 2 in your example is in cells A13:C14.
So, you want to return the Team for "Maria D." in cell C5.

The formula to enter into C5 should look like:

{=INDEX(C13:C14,MATCH(SUBSTITUTE(B4," ",""),A13:A14&B13:B14,0))}

Note that the squiggly brackets really are not part of the formula. They just indicate that the formula is an array formula that must be entered using CTRL-SHIFT-ENTER instead of just ENTER.

I found this link with helped me get there: https://www.deskbright.com/excel/index-match-multiple-criteria/
It pretty much follows that logic. I just had to remove the space in the name using the Substitute formula.

 

[jtakw]

Hi,

Here's another way, slightly shorter Array formula to be confirmed by CSE (Control, Shift, Enter), see below for instructions.

Book1
	A	B	C
1	Table 1
2	Value	Name	Team
3	10$	Maria D.	PR
4	20$	Stefan J.	RD
5
6
7	Table 2
8	First Name	Last Name	Team
9	Maria	D.	PR
10	Stefan	J.	RD
Sheet253

Cell Formulas
Range	Formula
C3	{=INDEX(C$9:C$10,MATCH(B3,A$9:A$10&" "&B$9:B$10,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Formula copied down.

 

[tanuxaxaxa]

Thank you, guys!

That worked perfectly.

 

[Joe4]

You are welcome.
Glad we were able to help.