Find Specific Text from Data Source - If cell contains certain wording have a different output.

[LikeablePringle]

So I am Struggling with finding a way to have a formula that will search column C for certain words that will then categorise them into column D.

For example with the text cell C2 contains "Zoom" Cell D2 = Software, Cell C3 contains Zipcar cell D3 = Travel, C4 Contains " Voxi", D4 = Telephone.

At the moment i am using drop down menus but going through a large amount of data makes this very time consuming.
This will need to be able to contain a multitude of inputs and outputs but just can't wrap my head around the formula needed.

Thank you!

 

[akshay27]

You're want to use XLOOKUP(VLOOKUP would also work, but I like using the former since it can be a lot more flexible )

Book1
	A	B	C	D	E
1	Column C	The Output you want		Lookup Array	Return Array
2	Zoom	Software		Zoom	Software
3	Zipcar	Travel		Zipcar	Travel
4	Zoom	Software		Voxi	Travel
5	Zoom	Software
6	Voxi	Travel
7	Voxi	Travel
8	Zoom	Software
9	Zipcar	Travel
10	Voxi	Travel
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=XLOOKUP(A2,$D$1:$D$4,$E$1:$E$4,"Nothing defined in the Return Array for this Lookup Value")

 

[LikeablePringle]

You're want to use XLOOKUP(VLOOKUP would also work, but I like using the former since it can be a lot more flexible )

Book1
	A	B	C	D	E
1	Column C	The Output you want		Lookup Array	Return Array
2	Zoom	Software		Zoom	Software
3	Zipcar	Travel		Zipcar	Travel
4	Zoom	Software		Voxi	Travel
5	Zoom	Software
6	Voxi	Travel
7	Voxi	Travel
8	Zoom	Software
9	Zipcar	Travel
10	Voxi	Travel
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=XLOOKUP(A2,$D$1:$D$4,$E$1:$E$4,"Nothing defined in the Return Array for this Lookup Value")

Thank you for that.

The issue i am having though is that i also need to search within the A column to identify if the key words are there.
As its bank statements there are multiple words in the Cell rather than just Zoom for example. Are we able to search for a specific word in column A then use an X lookup if said word is present?

 

[akshay27]

Thank you for that.

The issue i am having though is that i also need to search within the A column to identify if the key words are there.
As its bank statements there are multiple words in the Cell rather than just Zoom for example. Are we able to search for a specific word in column A then use an X lookup if said word is present?

The solution I came up with is a bit clunky, but works. If you could post the sheet you're dealing with here it might be a bit easier to help

Book2
	A	B	C	D	E
1	Your List	The output you want		Your Keywords	Categories for those keywords
2	zoom.com	Software		zoom	Software
3	zipcar.uk	Travel		zipcar	Travel
4	www.Zoom.at	Software		voxi	Telephone
5	https://voxi.com	Telephone
6	VOXI.gg	Telephone
7	www.ZipCar.com	Travel
8	https://Zoom.gg	Software
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(ISNUMBER(SEARCH($D$2,A2)),$E$2, IF(ISNUMBER(SEARCH($D$3,A2)),$E$3, IF(ISNUMBER(SEARCH($D$4,A2)),$E$4, )))

Also, apologies, the response I posted earlier was COMPLETELY wrong. The issue is, I realised much later and by then, I could no longer edit or delete that post. This solution should ideally work, but it assumes you have a very short list of keywords that you're looking for. the longer your list, the uglier the formula becomes.

I know there is a way to implement this more elegantly using some fancy array formulas, but I can't get it to work. The only other idea I have would be to use VBA.

 

[LikeablePringle]

The solution I came up with is a bit clunky, but works. If you could post the sheet you're dealing with here it might be a bit easier to help

Book2
	A	B	C	D	E
1	Your List	The output you want		Your Keywords	Categories for those keywords
2	zoom.com	Software		zoom	Software
3	zipcar.uk	Travel		zipcar	Travel
4	www.Zoom.at	Software		voxi	Telephone
5	https://voxi.com	Telephone
6	VOXI.gg	Telephone
7	www.ZipCar.com	Travel
8	https://Zoom.gg	Software
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(ISNUMBER(SEARCH($D$2,A2)),$E$2, IF(ISNUMBER(SEARCH($D$3,A2)),$E$3, IF(ISNUMBER(SEARCH($D$4,A2)),$E$4, )))

Also, apologies, the response I posted earlier was COMPLETELY wrong. The issue is, I realised much later and by then, I could no longer edit or delete that post. This solution should ideally work, but it assumes you have a very short list of keywords that you're looking for. the longer your list, the uglier the formula becomes.

I know there is a way to implement this more elegantly using some fancy array formulas, but I can't get it to work. The only other idea I have would be to use VBA.

That worked!

Your right about it being a bit of a faff working with a large keyword list - but i have been playing around with it and it works better than anything i could manage!

Thank you so much for your help