Using Excel, I have two separate workbooks with one worksheet in each that has one column for last names and one adjacent column for first names in the same (last then first) order.
I want the second workbook, for each row of names, to look at the first workbook for the same last+first name combo and return a value in a separate column on that row.
[ExperienceReport.xlsx]Ad Hoc Report:
Columns: A - B - C Last - First - Desired Return Last - First - Desired Return
Columns: H - I - X Last - First - Find and return Desired Return for this name Last - First - Find and return Desired Return for this name
So far, I've come up with the follwing, but I'm getting a #VALUE error on every row:
=INDEX('[ExperienceReport.xlsx]Ad Hoc Report'!$A$2:$J$150,MATCH($H5&" "&$I5,'[ExperienceReport.xlsx]Ad Hoc Report'!$A$2:$A$150&'[ExperienceReport.xlsx]Ad Hoc Report'!$B$2:$B$150,0),3)
Logically, I can't find the problem as it seems like it's going like this: INDEX(look in the specified array on the first workbook/worksheet for the row where MATCH(the 'last first' combo on this sheet matches the 'last first'combo in the A and B columns on the first workbook/worksheet) and give me the value in the 3rd column of that row.
I tend to do things the difficult way, I know, but if anyone can see a glaring error in my ways, please help!