Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I'm looking to add a Column B which auto populates with categories Based upon keywords in column A. Eg red = Colour or six = Numbers.
Is IF the best way to do this? There will be tens of keywords per Category.
Column A is already populated when I receive it.
Thanks
You need to make a list of ColA values and corresponding ColB values then you can create a lookup table elsewhere to refer to
You then use a function like VLOOKUP to search for values in ColA and return the answer in ColB
IF can work if there is a small number of categories, like 3, but if there are more then VLOOKUP or HLOOKUP would be easier.
I suspect you're going to have several colours and several numbers so VLOOKUP would work better.
A vlookup on a reference table is likely neater when you’ve got tens of possible answers; otherwise you get a very long nested if formula.
You say ‘keywords’, is the full value of column A what you’re looking for, or if it says “red coat” are you looking for the column B value based only on the fact it contains “red”?
Thanks all.
njee20 - it'd be looking for keywords in a web address so Not exact match.
That has a bearing, and makes things significantly messier!
You can use wildcards in Vlookup, which should help match buried keywords.
=VLOOKUP("*"&value&"*",data,2,FALSE)
See https://exceljet.net/formula/partial-match-with-vlookup