Capital letters in formula

[lukasz_rz]

Dear Experts.

I Have a table (example):
column AD column AE
CAT CT
dog DG
elephant EL

Then, I have a column (O2) where I try to find strings from the above (CAT, dog, elephant) and display those as CT,DG,EL in a new column.

INDEX(Lists!AE:AE;AGGREGATE(15;6;ROW(Lists!$AE$2:$AE$7)/(COUNTIF(O2;Lists!$AD$2:$AD$7)>0);1)

But, the formula displays CT for any of these: CAT, cat, Cat, etc. What I need is to display CT only when CAT string is found, is that possible?

Will appreciate your help, thanks.

 

[FormR]

Hi, here is one option you can try.

=LOOKUP(2;1/EXACT(Lists!$AD$2:$AD$1000;O2);Lists!$AE$2:$AE$1000)

 

[RoryA]

Perhaps:

=INDEX(Lists!AE:AE,AGGREGATE(15,6,ROW(Lists!$AE$2:$AE$7)/(EXACT(O2,Lists!$AD$2:$AD$7)+0),1))

 

[Toadstool]

Hi lukasz_rz,

Does this work for you?

lukasz_rz.xlsx
	O	P	Q												AC	AD	AE
1	Check	Result														Animal	Abbrev.
2	CAT	CT														CAT	CT
3	Cat															MOUSE	MS
4	Dog															DOG	DG
5	SHEEP	SH														PIG	PG
6	PIG	PG														SHEEP	SH
7	Pig
Lists

Cell Formulas
Range	Formula
P2:P7	P2	=IFERROR(INDEX(Lists!AE:AE,AGGREGATE(15,6,ROW(Lists!$AE$2:$AE$7)/(EXACT(O2,Lists!$AD$2:$AD$7)),1)),"")

 

[Alex Blakenburg]

Thanks FormR, have not seen the use of EXACT in a look before.
I googled the Match version it that and it would look like this.

=INDEX(Lists!AE:AE,MATCH(TRUE,EXACT(O2,Lists!AD:AD),0),0)

 

[lukasz_rz]

Toadstool.

It partially works, but there is one more element it does not cover. I'm not only looking for exact matches but also cases like: CAT is black (in Check column) which contains CAT (from Animal column), so should be marked as CT (Result column).

I should've mentioned exactly how my columns look like, sorry:
column AD column AE
*CAT* CT
*dog* DG
*elephant* EL

 

[RoryA]

You could use:

=LOOKUP(2,1/FIND(O2,Lists!$AD$2:$AD$1000),Lists!$AE$2:$AE$1000)

then you don't need the wildcards.

 

[lukasz_rz]

You could use:

=LOOKUP(2,1/FIND(O2,Lists!$AD$2:$AD$1000),Lists!$AE$2:$AE$1000)

then you don't need the wildcards.

I'm afraid it does not work.

 

[Toadstool]

Toadstool.

It partially works, but there is one more element it does not cover. I'm not only looking for exact matches but also cases like: CAT is black (in Check column) which contains CAT (from Animal column), so should be marked as CT (Result column).

I should've mentioned exactly how my columns look like, sorry:
column AD column AE
*CAT* CT
*dog* DG
*elephant* EL

This approach doesn't want the wildcards so the "*" are stripped:

lukasz_rz.xlsx
	O	P	Q												AC	AD	AE
1	Check	Result														Animal	Abbrev.
2	The CAT in the hat	CT														*CAT*	CT
3	The Black Cat															*MOUSE*	MS
4	Good Doggy															*DOG*	DG
5	GOOD DOGGY	DG														*PIG*	PG
6	PIG in a POKE	PG														*SHEEP*	SH
7	Pig															*GOAT*	GT
Lists

Cell Formulas
Range	Formula
P2:P7	P2	=IFERROR(INDEX(Lists!AE:AE,AGGREGATE(15,6,ROW(Lists!$AE$2:$AE$7)/(ISNUMBER(FIND(SUBSTITUTE(Lists!$AD$2:$AD$7,"*",""),O2))),1)),"")

 

[RoryA]

I'm afraid it does not work.

Meaning what exactly?