A reverse VLOOKUP?
I'm trying to achieve the following in Google Sheets.
Context - these are exported bank statements.
I have a sheet of lookup values (string, category).
| String | Category |
|---|---|
| ABC | Cat1 |
| DEF | Cat2 |
| GHI | Cat3 |
I have another sheet of data, in particular 1 column which is Description.
| Description | Amount |
|---|---|
| THE ABC SHOP TXN:1234567 | 100 |
| THE ABC SHOP TXN:3456789 | 200 |
| DEFINITELY ANOTHER SHOP REF: 1293891 | 500 |
The strings in the first sheet are to look up against as the Descriptions that contain both static and dynamic identifiers in the same column. In particular I need to assign that row a category based on the description against the sheet of regexes I have constructed so that I can do a pivot table against the data for reporting.
| Description | Amount | Category |
|---|---|---|
| THE ABC SHOP TEXAS TXN:1234567 | 100 | Cat1 |
| THE ABC SHOP DALLAS TXN:3456789 | 200 | Cat1 |
| DEFINITELY ANOTHER SHOP HOUSTON REF: 1293891 | 500 | Cat2 |
Ideally this would also be a ARRAYFORMULA.
I can't use a VLOOKUP because that's only works for a exact match.
I've also tried INDEX/MATCH but that causes issues if I try to put a REGEXMATCH inside the expression as I can't put in a range in the REGEXMATCH.
In a way I guess this is a reversed VLOOKUP but instead of finding a match on a target range, I want to find the row of first value that is contained within the source value.
You may try:
=map(A2:A,lambda(Σ,if(Σ="",,iferror(+tocol(index(if(search(E2:E,Σ),F2:F,)),3),"--"))))
- Query formula not grouping properly
- Using FILTER with multiple conditions on Google Sheet
- How to union ranges in google spreadsheets
- Formula for filtering list based off of key with multiple criteria in Google Sheets
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria
- Arrayformula Serial Number by Groups
- List rows in array when the cell contain number in the string
- ImportXML XPath issue using Google Sheets on a simple web scraping query
- How to find the Second Space from the right in Google Sheets?
- Find if cell contents exist in array
- OR function not working with arrayformula in Google Sheets
- Is there a way to COUNTIF the number of partial name matches (REGEXMATCH) within a column of cells?
- Using IMPORTXML on Google Sheets to extract image URLs
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- Using a reference cell as part of a column in a google sheets query
- Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?
- Using Named Functions with New Google Tables
- Google sheets query not returning words with special characters
- Trying to figure out why my GoogleSheets Date formula is not working
- Why is this conditional formatting rule not working in Google Sheets?
- Google Sheets ARRAYFORMULA with Partial Match and Multiple Criteria Not Returning Expected Results
- Why does my Google Sheets query simplificaiton result in an error?
- How do I set up a formula that matches columns between two sheets, while returning blank values for the first two rows regardless of values
- I do not understand the error message displayed when I try to use QUERY() function in Google Sheets
- In Google Sheets, how do I keep a cell blank based on two other cells' contents?
- Removing protection from a range of cells before automatically deleting with a google script based on date
- How to insert yesterday's date in googlesheet
- How use loop variable in column or row number?
- Google sheets - find the cell containing specific text
- Google sheets formula for showing remaining time per month for descending list of weeks