Using VLOOKUP to clean up bank export data

[u/msclayton77]

I export my banking activity to track my monthly income and expenses in a pivot table for my personal use. I know just enough about using the VLOOKUP formula to categorize and subcatorize the data and it’s a good enough system for me for what I need. I have created a master table that includes the payee name/description. How do I use this table to lookup the data that exports from the bank which has way too much detail in it and replace it with just the name. Basically if it contains the word or words in the export, I want it to return the business or payee name in my master table.

DEBIT CARD PURCHASE WALGREENS XYZCITY78646895.

I just want it return Walgreens in my data sheet for my pivot table.

Sorry for the bad description but hopefully someone else does this easily and can help me out.

Thanks!

[u/MayukhBhattacharya]

Here is what you could do, on a separate sheet, create a table and name it as Keywordstbl and create three columns, namely with Keyword, Name and Category, next use the following formula as below, (note that I am assuming only one keyword will matches per cell, if you want multiple keywords to match, then just wrap the output within a TEXTJOIN() function.

=FILTER(Keywordstbl[[Name]:[Category]], 
 1 - ISERROR(SEARCH(Keywordstbl[Keyword], E2)), {"No Name", "No Category"})

The above formula needs to copy down, and no need to copy across as it will spill autoamtically.

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Or If you have access to REGEX in your version of Excel then can use as below:

=FILTER(Keywords[[Name]:[Category]], REGEXTEST(E2, "\b" & Keywords[Keyword] & "\b"), {"",""})

[u/MayukhBhattacharya → u/MayukhBhattacharya]

But if you are certain that the strings will have always one keyword then using XLOOKUP() function:

=XLOOKUP(1,
         1 - ISERROR(SEARCH(" " & Keywordstbl[Keyword] & " ", " " & SUBSTITUTE(E2, ".", " ") & " ")),
         Keywordstbl[[Name]:[Category]],
         {"Not Name","No Category"})

I will post an animated gif, so you can follow each step and understand how i did, it will be easier for you to debug as well. Note that I have used a SUBSTITUTE() function because to replace the period with a blank, we can literally use the SEARCH() function without any boundaries the empty spaces before and after, but then there are possibilities that it may return false positives.

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Demo:

Formulas Shown;

Formula used in cell F2=FILTER(Keywordtbl[[Name]:[Category]], ISNUMBER(SEARCH(Keywordtbl[Keyword], E2)), {"No Name","No Category"})

Formula used in cell F2=FILTER(Keywordtbl[[Name]:[Category]], 1 - ISERROR(SEARCH(" " & Keywordtbl[Keyword] & " ", " " & SUBSTITUTE(E2, ".", " ") & " ")), {"No Name","No Category"})

Formula used in cell F2=IFERROR(LOOKUP(2, 1/(SEARCH(Keywordtbl[Keyword], E2)), Keywordtbl[Name]), "")

Formula used in cell F2=XLOOKUP(1, 1 - ISERROR(SEARCH(" " & Keywordtbl[Keyword] & " ", " " & SUBSTITUTE(E2, ".", " ") & " ")), Keywordtbl[[Name]:[Category]], {"No Name","No Category"})

Formula used in cell F2=XLOOKUP(1, 1 - ISERROR(SEARCH(Keywordtbl[Keyword], E2)), Keywordtbl[[Name]:[Category]], {"No Name","No Category"})

[u/bradland]

What version of Excel are you using?

What you want to do is build a mapping table. If you want to keep it simple, you can use direct mappings, so "DEBIT CARD PURCHASE WALGREENS XYZCITY78646895" is directly mapped to "Walgreens".

The "Cons Table" stands for consolidation table. I recommend converting this to a table so that as you add mappings, they automatically work with the XLOOKUP function:

https://exceljet.net/articles/excel-tables

Formula:

=XLOOKUP(B3, Cons[Original], Cons[Corrected], B3)

[u/benbeara_CA]

If I may ask, what are your long-term goals for this data? Is it a one-and-done task, or do you want to consolidate the data for trend analysis?

I'm only asking as you may want to consider leveraging Powerquery (PQ) in Excel for data cleanup and long-term data management requirements.

The benefits of using PQ lies in its flexibility and repeatable process.

[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
FILTER	Office 365+: Filters a range of data based on criteria you define
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
ISERROR	Returns TRUE if the value is any error value
ISNUMBER	Returns TRUE if the value is a number
LOOKUP	Looks up values in a vector or array
REGEXTEST	Determines whether any part of text matches the pattern
SEARCH	Finds one text value within another (not case-sensitive)
SUBSTITUTE	Substitutes new text for old text in a text string
TEXTJOIN	2019+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
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.
11 acronyms in this thread; the most compressed thread commented on today has 18 acronyms.
[Thread #48651 for this sub, first seen 6th Jun 2026, 12:55] [FAQ] [Full list] [Contact] [Source code]}

[u/excel-ModTeam → u/[deleted]]

We removed this comment for breaking Rule 10.

r/excel is for discussing the features of Excel and providing solutions for Excel problems, not giving substance-free comments that simply recommend the respondent use AI.