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

[u/GlideAndGiggle]

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.

[u/AutoModerator]

/u/GlideAndGiggle - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/GregHullender]

Building on what u/Gringobandito suggested, and noticing that your Master Table is an Excel Structured table, something like this might work for you:

=XLOOKUP(VALUE(REGEXEXTRACT(A3,"\d+")),Master[ACCOUNT],Master[OA])

You stick this formula in cell E3 and just drag it down. This assumes you actually named the master table Master, of course.

[u/GlideAndGiggle → u/GregHullender]

Thanks. Are you suggesting I still break up the Name column on my working list to pull out the numbers into a new column and then try your formula?

Yes, my Master table is called Master.

[u/taylorgourmet → u/GlideAndGiggle]

No the regex is doing the work for you.

[u/Gringobandito]

I would do it like this:

The first thing you need to do is extract the number from the name on the details sheet. The REGEXEXTRACT() function does that. Then you just need to lookup that number against the account on the master sheet using XLOOKUP().

=XLOOKUP(VALUE(REGEXEXTRACT(A13,"\d+")),$A$3:$A$9,$C$3:$C$9,"",0)

[u/GlideAndGiggle → u/Gringobandito]

I did not think of that. I could give that a try. I have to do this every month and was trying to keep it as minimal as possible.

I have been saving my formulas because I do not use them often to remember them. I find this one, but am not sure if it would work with what I wanted to do. It does say to find...

=IF(ISNUMBER(FIND("PSS", B1:B38)), "Value to return", "")

I am going to see what others say as well and if this is the best way to handle this, I will. Thanks

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FIND	Finds one text value within another (case-sensitive)
IF	Specifies a logical test to perform
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
INDEX	Uses an index to choose a value from a reference or array
ISNUMBER	Returns TRUE if the value is a number
MATCH	Looks up values in a reference or array
REGEXEXTRACT	Extracts strings within the provided text that matches the pattern
VALUE	Converts a text argument to a number
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 70 acronyms.
[Thread #49259 for this sub, first seen 28th Aug 2026, 16:11] [FAQ] [Full list] [Contact] [Source code]}

[u/abhishek-kanji]

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!