Hello, I have two Spreadsheets. In the ExcelDocSummary Spreadsheet, I need an Index MatchLookup based upon two columns: DocId and RevNo to retrieve information from the ExcelDocDetail Spreadsheet and insert it in the orange formula area via a live formula. Both are separate spreadsheet files, and both are formatted as tables, with table headers noted.

I know how to do an Index Match on one column in a spreadsheet to another, but not two columns to two columns.

Here are my two spreadsheets:

• Edited by vsla 15 hours 11 minutes ago
• Moved by Lanlan HuangMicrosoft contingent staff 5 hours 28 minutes ago

Open both workbooks.

Enter the following array formula confirmed with Ctrl+Shift+Enter in C2 on ExcelDocSummary.xlsx:

=IFERROR(INDEX('ExcelDocDetail.xlsx!Sheet1'!$C$1:$C$100, MATCH(A2&B2, 'ExcelDocDetail.xlsx!Sheet1'!$A$1:$A$100&'ExcelDocDetail.xlsx!Sheet1'!$B$1:$B$100, 0)), "")

Adjust the ranges as needed, then fill down.

Hans, Thank you - it doesn't appear that this works quite right as the two spreadsheets are formatted as tables - I think there is a slightly different twist to this with tables. Is that true ?

But also not having luck with it outside of the table, and just in plain cells.

For tables, this should work. But remember that you must confirm the formula with Ctrl+Shift+Enter:

=IFERROR(INDEX('ExcelDocDetail.xlsx'!Table1[Description],MATCH([@DocID]&[@RevNo],'ExcelDocDetail.xlsx'!Table1[DocID]&'ExcelDocDetail.xlsx'!Table1[RevNo],0)),"")

where Table1 is the name of the table in ExcelDocDetail.xlsx.

Thank you!

Hi vsla,

I am glad your issue has been resolved, and this forum is discussing about Excel developing like Excel automation, Excel Customization. Your issue is about Index MatchLookup which is a product issue, I will move this thread to the correct forum.

In addition, if you have any issues about Excel product, I will recommend you post your issue in the forum below:

Excel IT Pro Discussions: http://social.technet.microsoft.com/Forums/en-US/home?forum=excel

Best Regards,

Edward