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

Current worksheet:

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!


The easy way is to make a helper column on both sheets that contains the first and last name and use regular VLOOKUP(). But there is also a "hard" way.

INDEX() and MATCH() can only return a single cell or item. What you have to do is combine your columns into a single array inside your formula like this:

=INDEX('[ExperienceReport.xlsx]Ad Hoc Report'!$C$2:$C$150,MATCH(H2&" "&I2,'[ExperienceReport.xlsx]Ad Hoc Report'!$A$2:$A$150&" "&'[ExperienceReport.xlsx]Ad Hoc Report'!$B$2:$B$150,0))

Or, more generally:

=INDEX([Return range],MATCH(arg1&arg2...,[arg1 range]&[arg2 range]...,0))

This is an array formula and must be confirmed with ctrl+shift+enter.

  • Turns out I'm an idiot and breezed right over the requirement to enter this as an array formula. My formula was correct but I didn't hold CTRL+SHIFT when I hit ENTER. It's working correctly now, Thank you! – Andrew Jan 14 '16 at 18:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.