excel index match with formula in a table using headers not working

[leefletcher]

I am trying to do an index/match using column headings in a table where there is a formula creating the information in the lookup array, but I'm getting an error. Table 1 has the First and Last names with a formula to create the full name. Table2 has the Full Name and the school I want to return. If I copy and paste the Full Name over the formula in Table1, the formula in School in Table2 returns the correct School from Table1.

Table1

First Name	Last Name	Full Name*	School
David	Chaudhri	=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])	Bayshore Conservatory
Reinhard	Vinter	=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])	River Valley Institute
Emma	Coeman	=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])	Ravenwood School for Girls
Pharaildis	Christensen	=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])	Bayshore Academy
Phanuel	Barsamian	=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])	Maple Park High School

*I used Proper because the First and Last Names are imported from another source that does not have standard formatting.

Table2

Full Name	School
David Chaudhri	=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")
Reinhard Vinter	=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")
Emma Coeman	=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")
Pharaildis Christensen	=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")
Phanuel Barsamian	=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")

Is there a way to pull first and last name together so that the formula works?

 

[Fluff]

There is nothing wrong with your formula, check the names in both tables, do not have any leading/trailing spaces.

 

[leefletcher]

There is nothing wrong with your formula, check the names in both tables, do not have any leading/trailing spaces.

Thank you for answering me. I think the names are ok, and it works if I put the actual names in Table1. I don't see a way to share my spreadsheet but I cut and pasted the above directly from it.

 

[Fluff]

What exactly do you mean by

it works if I put the actual names in Table1

 

[leefletcher]

What exactly do you mean by

In Table1, there is a Formula in Full Name. When I replace the formula with the names as text, the formula in Table2 works. However, when I leave the Table1 formula as is, the formula in Table2 doesn't work.

 

[RoryA]

Then there must be something off with your data in Table1 as fluff said. Check (again) for leading/trailing spaces. Incidentally, you can just use:

=PROPER([@[First Name]]&" "&[@[Last Name]])

and:

=IFERROR(INDEX(Table1[School],MATCH([@[Full Name]],Table1[Full Name],0)),"")

 

[Fluff]

What happens if you trim the names like

=PROPER(TRIM([@[First Name]])&" "&TRIM([@[Last Name]]))

 

[leefletcher]

Then there must be something off with your data in Table1 as fluff said. Check (again) for leading/trailing spaces. Incidentally, you can just use:

=PROPER([@[First Name]]&" "&[@[Last Name]])

and:

=IFERROR(INDEX(Table1[School],MATCH([@[Full Name]],Table1[Full Name],0)),"")

Thank you for helping. The second formula creates a circular reference and I'm not seeing any extra spaces in the First and Last Name Data.

 

[leefletcher]

What happens if you trim the names like

=PROPER(TRIM([@[First Name]])&" "&TRIM([@[Last Name]]))

No change. Table2 formula still not working.

 

[Fluff]

The formulae work for me, so there is something wrong with the data.

Fluff.xlsm
	A	B	C	D	E	F
1
2
3		First Name	Last Name	Full Name	School
4		David	Chaudhri	David Chaudhri	Bayshore Conservatory
5		Reinhard	Vinter	Reinhard Vinter	River Valley Institute
6		Emma	Coeman	Emma Coeman	Ravenwood School for Girls
7		Pharaildis	Christensen	Pharaildis Christensen	Bayshore Academy
8		Phanuel	Barsamian	Phanuel Barsamian	Maple Park High School
9
10
11
12
13
14		Full Name	School
15		David Chaudhri	Bayshore Conservatory
16		Reinhard Vinter	River Valley Institute
17		Emma Coeman	Ravenwood School for Girls
18		Pharaildis Christensen	Bayshore Academy
19		Phanuel Barsamian	Maple Park High School
20
Sheet1

Cell Formulas
Range	Formula
D4:D8	D4	=PROPER(TRIM([@[First Name]])&" "&TRIM([@[Last Name]]))
C15:C19	C15	=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")