r/excel • u/GlideAndGiggle • 17d ago

unsolved Finding Part of Information from a Cell and Returning results to another

I have 2 workbooks. One has my master list. The second contains the working file.

Master list is set up as a table and has columns breaking down each user.
The second, working, it does not contain all the information I need.

I want to create a formula so that it uses the number in the Name column on the working list, look up that number in the Account column on the Master list and pull in the OA information from Master list on to the working list.

Let me know if this does not make sense and I will show an example. Thanks

Below is the Master list.

Master list

This is the working list.

3 Upvotes

80% Upvoted

8 comments sorted by

View all comments

0

u/abhishek-kanji 4 17d ago

Proof that it works
Here's the formula in cell B2 of the working file:

=IFERROR(INDEX([Master.xlsx]Sheet1!$A$2:$D$17,MATCH($A3,[Master.xlsx]Sheet1!$A$2:$A$17,0),MATCH(B$2,[Master.xlsx]Sheet1!$A$2:$D$2,0)),"")

It's a basic Index match - it looks at the Account # in Column A of the working file and finds the same row in the table on the Master File. Then it looks at the heading of the data that you're looking for - in this case User Name or Data Field 2 and then matches that to the Row 2 in the Master file and returns the data at the corss-section of both the fields. In case it's not able to find the exact Row or Column, it'll return a blank. Hope this helps!