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)))))
- Not able to iterate through all the rows in 2D array in Row-Wise Binary Search
- How can I remove a specific item from an array in JavaScript?
- Get the last item in an array
- Searching for part of a string within an array
- Fastest way to convert Image to Byte array
- How to quickly find the minimum element to the right for every element of a Numpy array?
- JS sort array of pair with null and empty values at the end
- bash script to Echo multiple lists
- storing char in char array - incompatible types in assignment of char to char[]
- Inserting JSON data with 'insertAdjacentElement' instead of 'innerHTML'
- Extract associative array elements into individual variables
- How can I loop through enum values for display in radio buttons?
- Split a comma-delimited string into an array?
- How to transform array into desired output using jolt
- How to override only method signature in typescript?
- How do I remove an element from an array at a specified index but not do the operation in-place
- Using array values to select database table in laravel
- Custom Fields - Repeater dropdown - list only unique & not blank values
- Get taxonomies in a category archive page
- How can I compare a clicked elements ID with another ID?
- Unexpected memory allocation when using array views (julia)
- How can I check if a lua table contains only sequential numeric indices?
- Snowflake - challenge in accessing objects in an array
- Convert a Result Set from SQL Array to Array of Strings
- How to convert an array into an object?
- How do I know an array contains all zero(0) in Javascript
- JS create array from a sorted array of pairs
- Insert array into MySQL database with PHP
- How to explode only on the last occurring delimiter?
- Java two varargs in one method