How do I find a partial match from a data table to an individual cell?
I'm trying to build a budgeting spreadsheet, and I want to automatically categorize expenses that have certain key words, if available. The output from the credit card company contains descriptions, and I want to build a generalized database of common expenses in categories, and build a lookup that'll pull the related category.
I'm linking below to an example spreadsheet that has some selected data to show what I'm trying to do.
https://docs.google.com/spreadsheets/d/1b-uWQLJJhFaXNwKj7iY4YdcmnaRpttTKh9nzkgDd6iU/edit?usp=sharing
Column C is what I want to use to look up the category. The categories are in J1:AJ6. What I want the output to be is in column I. Row 13 is a good example - Amazon puts a unique identifier at the end, so I just want AMZN to match from column Z and pull the category "Misc" in.
If you don't want to look at the sheet, below is a dummy representation of what I'm trying to do, even more simplified
| CC Data Description | Desired Lookup Result | Misc | Food and Groceries | Pet | Travel | Entertainment | Fast Food |
|---|---|---|---|---|---|---|---|
| NETFLIX.COM | Entertainment | TARGET | H-E-B | CHEWY.COM | LYFT | NETFLIX.COM | CHICK-FIL-A |
| CHEWY.COM | Pet | AMZN | KROGER | UNITED | Prime Video | MCDONALD'S | |
| DOUBLETREE NEWCASTLE | SH SALON | UBER | |||||
| TARGET 00034215 | Misc | HILTON | |||||
| AMZN Mktp US*HB2XZ9AC3 | Misc | THEPARKINGSPOT | |||||
| MCDONALD'S F11327 | Fast Food | ||||||
| LEGENDS@NISSAN-ZIPPIN | |||||||
| LYFT *1 RIDE 09-22 | Travel |
Here's one approach you may test out:
=map(C2:C,lambda(Σ,if(Σ="",,iferror(+tocol(index(if(len(J2:AJ6)*search(J2:AJ6,Σ),J1:AJ1,)),3,1)))))
- How can i take an array, divide it by two and create two lists?
- Most elegant way to convert string array into a dictionary of strings
- In a java enhanced for loop, is it safe to assume the expression to be looped over will be evaluated only once?
- Can't exploit properly an array with twig
- how to use document.getElementById to display the textfield from table using combobox select
- Weird compiler optimization/behavior regarding a "for" loop without a body in Objective-C
- How can I split a string into segments of n characters?
- How to convert int[] to byte[]
- Group rows of a 2d array by a column and create subarrays
- Declare individual variables based on the key names in a 2d array
- Speed up numpy looking for best indices
- Does JavaScript's Array.prototype.fill() always create a packed array?
- Print array without brackets and commas
- Two way binding an array of strings in ng-repeat
- how to pass JSON data though a foreach loop in php
- Pass array value in query post in WordPress
- LINQ - How to correctly group a list of users by their nested list of values
- How can I remove a specific item from an array in JavaScript?
- Remove empty strings from array while keeping record Without Loop?
- What should be the type expression of a 2D array?
- How do I check if an array includes a value in JavaScript?
- Split a 2d array into two 2d grouping arrays by column value
- Jmeter JSON Extractor for Value based off another Value in Array
- Append or prepend to array without VBA/UDF
- JSON schema to enforce array contents
- Javascript: How to implement iterator over multidimensional array?
- Geometric series: calculate quotient and number of elements from sum and first & last element
- How to filter an array to get specific columns in two different objects?
- How to overwrite an array?
- what is array decay in c and when it happen?