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

leefletcher

New Member
Joined
Mar 22, 2018
Messages
27
Office Version
  1. 2019
Platform
  1. Windows
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 NameLast NameFull Name*School
DavidChaudhri=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])Bayshore Conservatory
ReinhardVinter=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])River Valley Institute
EmmaCoeman=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])Ravenwood School for Girls
PharaildisChristensen=PROPER([@[First Name]])&" "&PROPER([@[Last Name]])Bayshore Academy
PhanuelBarsamian=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 NameSchool
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
There is nothing wrong with your formula, check the names in both tables, do not have any leading/trailing spaces.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:

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

and:

Excel Formula:
=IFERROR(INDEX(Table1[School],MATCH([@[Full Name]],Table1[Full Name],0)),"")
 
Upvote 0
What happens if you trim the names like
Excel Formula:
=PROPER(TRIM([@[First Name]])&" "&TRIM([@[Last Name]]))
 
Upvote 0
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:

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

and:

Excel Formula:
=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.
 
Upvote 0
The formulae work for me, so there is something wrong with the data.
Fluff.xlsm
ABCDEF
1
2
3First NameLast NameFull NameSchool
4David ChaudhriDavid ChaudhriBayshore Conservatory
5ReinhardVinterReinhard VinterRiver Valley Institute
6EmmaCoemanEmma CoemanRavenwood School for Girls
7PharaildisChristensenPharaildis ChristensenBayshore Academy
8PhanuelBarsamianPhanuel BarsamianMaple Park High School
9
10
11
12
13
14Full NameSchool
15David ChaudhriBayshore Conservatory
16Reinhard VinterRiver Valley Institute
17Emma CoemanRavenwood School for Girls
18Pharaildis ChristensenBayshore Academy
19Phanuel BarsamianMaple Park High School
20
Sheet1
Cell Formulas
RangeFormula
D4:D8D4=PROPER(TRIM([@[First Name]])&" "&TRIM([@[Last Name]]))
C15:C19C15=IFERROR(INDEX(Table1,MATCH([@[Full Name]],Table1[Full Name],0),MATCH(Table2[[#Headers],[School]],Table1[#Headers],0)),"")
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top