I have these 2 files. both of them have common field called "Organization Name". can you please help me link these 2 files and display all data in one file.organization.xlsxnote.xlsx
I have these 2 files. both of them have common field called "Organization Name". can you please help me link these 2 files and display all data in one file.organization.xlsxnote.xlsx
I'm generally a proponent of the INDEX(MATCH) method. It will work better if your data is in one workbook, though it's possible to do a data connection, as well.
This will work with them in separate workbooks, as long as both are open when you write it in the first time:
In the Organization.xlsx file, add two new column headers for the columns in note.xlsx, in cells E1 and F1.
Type this formula in cell E2:
Copy it down and over.Please Login or Register to view this content.
INDEX looks for a cell by the row and column reference. MATCH will return a reference for where an item exists in a range (5th cell down, 2nd cell across, etc). What we're doing here is using the first MATCH to find which row has the same organization name, and the second MATCH to find which column has the same header name.
In the first MATCH, we use a dollar sign ($) to lock the Column reference (A) so that it always shows the organization column, even if you copy the formula to columns further right.
In the second MATCH, we use the same dollar sign ($) to lock the Row reference (1) so that it always shows the header, even if you copy the formula further down.
The IFERROR portion shows "Not Found" rather than "#N/A", just to give context to the error message.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks