Replace mulitple IF(ISNUMBER(SEARCH(" with vlookup?

Sorry, I know this is a duplicate question, but I just can't seem to get my head around vlookup. Hopefully I explain myself clearly:


This is what I have now and want to replace with something not as time consuming to mainatain - Short example:

Sheet: CAR

Enter in column C:

=IF(ISNUMBER(SEARCH("vOLT*",B:B)),"cHEVY",IF(ISNUMBER(SEARCH("*Golf*",B:B)),"VM",IF(ISNUMBER(SEARCH("*Jetta*",B:B)),"VM")))


What I am trying to do above is say:
Search column B, for the words like 'volt', if like volt is found (or even volted), in Column C put the words Chevy, or if Volt<anything> not found, search for "*Golf*", and continue/repeat/go through the list I have made.

Column B - "Car Models" a listing of various car models. Car model types listed more than once.
Column C - blank with formula. Heading, "Categorize Manufacture"

Instead what I want to do is create a new sheet called Sheet2 and have a table that outlines - column A all of the possible car models (volt, jetta, golf...etc), and list in column B their corresponding manufacture (chevy,VW,ford, etc...).

THEN, back to my original Sheet:CAR, I want to somehow call in column C my new formula to look in Column B for car model names 'like' (not exact spelling, some names are incomplete or spelled various ways and with spaces) and enter the corresponding car manufacture that I have outlined in my table in Sheet2.

Does that make sense? Thanks in advance!

Sorry, it has taken me awhile to respond, but I'm now back online. I've simplied my spreadsheet for the purpose of the example - see attached (car.xlsx).

Sheet called "Car" includes the formula I'm using.
Sheet called "Table" includes the table I want to somehow call into my formula to reduce the length of my formula

Forumla I want to reduce - [=IF(ISNUMBER(SEARCH("*vOLT*",B:B)),"cHEVY",IF(ISNUMBER(SEARCH("*Golf*",B:B)),"VM",IF(ISNUMBER(SEARCH("*Jetta*",B:B)),"VM")))]

@daddylonglegs - I just tried the formula you provided in my example spreadshet & it worked . Though I have no idea what I am reading - lol. I will paste the formula in my big sheet and see if still works. Thanks.

Super excited, that works! Thank-you so much. I will close this thread.

Originally Posted by daddylonglegs

If your table is in sheet2 A2:B100 then try this formula in CAR sheet C2 copied down

=IFERROR(LOOKUP(2,1/SEARCH(Sheet2!A$2:A$100,B2)/(Sheet2!A$2:A$100<>""),Sheet2!B$2:B$100),"No match")

It's perfectly working fine, simply amazing skills

Originally Posted by Glenn Kennedy

wk9128. This thread is 10 years old!!

Oh my goodness, I deleted it