Nested confusion

[mhumphries]

Hello,
Looking for a solution to
Column K2:K126 contains different text search for a string (_X1 or _N1 or _S1, or _A10)
X = Hawk
N = Sparrow
S = Eagle
A = Owl

What i am wanting to do is, if(K2:K31 has _X1 return value Hawk or if (K2:K31) has _N1 then return value Sparrow etc

was looking at =IF(COUNTIF(K2:K31,"*_X*"),"Hawk","") this works, now need to expand to get other scenario to work
=IF(COUNTIF(K2:K31,"*_X*"),"Hawk",if(K2:K31,"*_N*","Sparrow,) or something....

 

[JoeMo]

What do you want the formula to return if more than one search string is in the range?

 

[mhumphries]

What do you want the formula to return if more than one search string is in the range?

I need the value to be either Hawk or Sparrow or Eagle or Owl depending on the value in the string

 

[JoeMo]

I need the value to be either Hawk or Sparrow or Eagle or Owl depending on the value in the string

That doesn't answer the question: if more than one of the search strings is present in the range to be searched, what should the formula return?

 

[mhumphries]

That doesn't answer the question: if more than one of the search strings is present in the range to be searched, what should the formula return?

Contents of column K is

00TUT_X1

00TUT_X3

00TUT_X2

00TUT_X4

00TUT_N1

00TUT_S1

00TUT_S3

00TUT_X3

00TUT_A3

00TUT_S4

00TUT_X5

00TUT_A1

00TUT_A2

00TUT_N3

<colgroup><col></colgroup><tbody>
</tbody>

In column M, I want to have either Hawk, Sparrow, Eagle or Owl.
In the column K data the _X should give Hawk in column M
In the column K data the _A should give Eagle in column M etc

Output needs to look like
Column K Column M

00TUT_X1		Hawk
00TUT_X3		Hawk
00TUT_X2		Hawk
00TUT_X4		Hawk
00TUT_N1		Eagle
00TUT_S1		Sparrow

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[JoeMo]

Copy the formula in M1 down.

Excel Workbook
	K	L	M
1	00TUT_X1		Hawk
2	00TUT_X3		Hawk
3	00TUT_X2		Hawk
4	00TUT_X4		Hawk
5	00TUT_N1		Sparrow
6	00TUT_S1		Eagle
7	00TUT_S3		Eagle
8	00TUT_X3		Hawk
9	00TUT_A3		Owl
10	00TUT_S4		Eagle
11	00TUT_X5		Hawk
12	00TUT_A1		Owl
13	00TUT_A2		Owl
14	00TUT_N3		Sparrow
Sheet6

 

[shg]

Another way:

Row\Col	A​	B​	C​
1​	Input​	Output​	​
2​	00TUT_X1	Hawk	B2: =LOOKUP(99, FIND("_" & {"A","N","S","X"}, A2), {"Owl","Eagle","Sparrow","Hawk"})
3​	00TUT_X3	Hawk
4​	00TUT_X2	Hawk
5​	00TUT_X4	Hawk
6​	00TUT_N1	Eagle
7​	00TUT_S1	Sparrow
8​	00TUT_S3	Sparrow
9​	00TUT_X3	Hawk
10​	00TUT_A3	Owl
11​	00TUT_S4	Sparrow
12​	00TUT_X5	Hawk
13​	00TUT_A1	Owl
14​	00TUT_A2	Owl
15​	00TUT_N3	Eagle

 

[mhumphries]

Another way:

Row\Col	A​	B​	C​
1​	Input​	Output​
2​	00TUT_X1	Hawk	B2: =LOOKUP(99, FIND("_" & {"A","N","S","X"}, A2), {"Owl","Eagle","Sparrow","Hawk"})
3​	00TUT_X3	Hawk
4​	00TUT_X2	Hawk
5​	00TUT_X4	Hawk
6​	00TUT_N1	Eagle
7​	00TUT_S1	Sparrow
8​	00TUT_S3	Sparrow
9​	00TUT_X3	Hawk
10​	00TUT_A3	Owl
11​	00TUT_S4	Sparrow
12​	00TUT_X5	Hawk
13​	00TUT_A1	Owl
14​	00TUT_A2	Owl
15​	00TUT_N3	Eagle

<tbody>
</tbody>

2 solutions, Both work, thanks guys!!