Vlookup tweak to also look up partial string match

[muhleebbin]

Is it possible to have a vlookup do it's traditional function but then also find partial matches?

We have to fill in a vendor number for a credit card transaction form but the import from credit card statement descriptions are typically static however for fuel like Shell or Chevron they have unique numbers according to their locations. Rather than have 100entries for the various Shell's and Chevron's we go to (plus any new ones) would it be possible to find if the string contains "Shell" and still run the vlookup?

 

[michaelm0162]

How i would do it is create a supporting column that pulls the data out using RIGHT, LEFT, or MID formulas

i.e

1234567Chevron89 - Transaction line
i would create a support column to type =mid('cell location',find("Chevron",'cell location',1),len("Chevron"))

You could link "Chevron" to a given cell that just says "Chevron" so it can be shell, or AMPM or whatever and it adjusts to those lengths.

 

[michaelm0162]

From there you can Index, match or vlookup if you put that formula on the left side.

Or if it doesn't have a name and
"1234567" is the code for Chevron consistent out of a string of 1234567abcde357235

Use that LEFT, RIGHT, MID to extract that data into a support column still that it then reads off of.

 

[muhleebbin]

Thanks Michael for the suggestion, I think i'm following but can you clarify what you mean in regards to:

You could link "Chevron" to a given cell that just says "Chevron" so it can be shell, or AMPM or whatever and it adjusts to those lengths.

Below is a sample of the 'table array' that would be used in the vlookup/index match formula:

AP - Template.xlsm
	A	B
251	GODADDY.COM 480-505-8855 AZ	207
252	GRANQUARTZ MOBILE 00NORCROSS GA	212
253	THE HOME DEPOT SEATTLE WA	110
254	IDAHO LUMBER HAILEY ID	226
255	OFFICE DEPOT #1078 0KENT WA	273
256	SHELL OIL 5744561310NORTH BEND WA	396
257	SHELL OIL 5744403210SEATTLE WA	396
258	SHELL OIL 5744466590KETCHUM ID	396
Vendor IDs

Items in green are static, yellow - if we went to a new location it wouldn't find it, orange is an example of multiple entries but are the same vendor. Ideally i'd like a vlookup or index match function to be able to do it's typical function but then account for the variables like yellow and orange.

Hope that makes sense

 

[michaelm0162]

I found this as a quick google search for using cell color in formulas but I have never done that.
From what I am understanding you want like a vlookup(ORANGE,etc.,etc.)

Formula or function for IF statement based on cell color

I don't know how to code in VBA but am trying to automate an if/then calculation based on cell color. As shown in the picture, if the colors of the cells..

techcommunity.microsoft.com

 

[muhleebbin]

Hi Michael,

Sorry about the delay in response, just been slammed at work.

I only color coded the data to give specific examples of the different type of data. Green items - I could have as static entries in a traditional vlookup i.e. Granquartz Mobile in column A Granquartz in column B, Yellow - are items that if we went to a new store like in the Office Depot case then we would need to have an entry for each location we go to, like the examples in orange. Rather than do that, if the vlookup could look up traditional but also look up variable like Office Depot ##### and bring up Office Depot.