CODEWITHSUNDEEP
google-sheetsvlookuparray-formulas
Abhay
Abhay

Reputation: 845

Google Sheets: vLookup with partial matches

I'm running an ad campaign for an auto client. I have a sheet in which I get campaign performance numbers against the ads that ran for the campaign. In the ad name, various parameters are defined such as the dealership name, car model, and website that the ad ran on. I need to use array formulas to extract the dealership name, and car model from the ad name.

I know how to use arrayformula() with multiple IF(REGEXMATCH()) to do the job but that method is getting cumbersome as there are more ads added every week and updating the formula is becoming a task in itself. I want to build a lookup table that the operations team can update and the information gets updated automatically in the sheet that pulls data.

I've set up a sample sheet to demonstrate this: https://docs.google.com/spreadsheets/d/1GcUoCEe209Ik5VvBu--xP1Vu8zX9Tn1_nJ8YLhA8DlI/edit?usp=sharing

In the data sheet, columns F, G, H need to be updated with the lookup with a partial match. The lookup table can be found in the lookup tab.

I'd appreciate any help I can get with setting this sheet up!

Upvotes: 0

Views: 349

Answers (1)

JPV
JPV

Reputation: 27292

In M2 in entered

=ArrayFormula(if(len(A2:A), proper(regexreplace(split(regexextract(A2:A, "_(.*)$"), "_"), "(\d+)", " $1")),))

As a possible alternative (without the need for a lookup table) I alse entered in Q2

=ArrayFormula(if(len(A2:A), proper(regexreplace(split(regexextract(A2:A, "_(.*)$"), "_"), "(\d+)", " $1")),))

See if that works for you ?

Upvotes: 1

Related Questions