If/Find/Search??

[Fazila]

I'm not really sure how to do this or even if there is a way it can be done (but I'm really hoping it can).

I need to find the days when a specific subject is taught. The subject name will always contain the characters 11a/En which will be followed by 1, 2, 3, 4 etc. At the moment my table looks something like this:

	AMon1	AMon2	AMon3	ATues1
AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1
HGE	8c/Te1	11a/En2	8b/Re1	7a/En1

<tbody>
</tbody>

So basically I need a formula that will tell that an 11a/En class will be taught at AMon2 and AMon3 - is there any way this can be done?

Thanks

 

[jtakw]

Hi,

You didn't specify how you want the results, this is one way to identify the "days" for the subject you're searching:

Excel 2010
	A	B	C	D	E
1		AMon1	AMon2	AMon3	ATues1
2	AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1
3	HGE	8c/Te1	11a/En2	8b/Re1	7a/En1
4			AMon2	AMon3
5	11a/En		AMon2	AMon3
6	11a/Ma	AMon1
7	8b/Pe				ATues1
Sheet14

Cell Formulas
Range	Formula
B4	=IF(ISNUMBER(LOOKUP(2,1/SEARCH("11a/En*",B2:B3))),B1,"")
B5	=IF(ISNUMBER(LOOKUP(2,1/SEARCH($A5&"*",B$2:B$3))),B$1,"")

Use B4 formula where the Subject (11a/En) is hardcoded in the formula, Or
Use B5 formula if you might have Other Subjects you want to search for, enter the subject name of your choice, in my sample, A5:A7

B4 formula copied across.
B5 formula copied down and across.

 

[jtakw]

Hi,

Just realized, we don't need the 1/ within the above formulas, those can be taken out like so:

Excel 2010
	A	B	C	D	E
1		AMon1	AMon2	AMon3	ATues1
2	AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1
3	HGE	8c/Te1	11a/En2	8b/Re1	7a/En1
4			AMon2	AMon3
5	11a/En		AMon2	AMon3
6	11a/Ma	AMon1
7	8b/Pe				ATues1
Sheet14

Cell Formulas
Range	Formula
B4	=IF(ISNUMBER(LOOKUP(2,SEARCH("11a/En*",B2:B3))),B1,"")
B5	=IF(ISNUMBER(LOOKUP(2,SEARCH($A5&"*",B$2:B$3))),B$1,"")

 

[Aladin Akyurek]

I read the request a bit differently...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		AMon1	AMon2	AMon3	ATues1
2​	AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1		11a/En	AMon2	AMon3
3​	HGE	8c/Te1	11a/En2	8b/Re1	7a/En1

In H2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH("|"&$G2,"|"&$B$2:$E$3)),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),COLUMNS($H2:H2))),"")

 

[Fazila]

The formula works but there would be too many blanks in between i.e. given the example above the result would be

column G column H column I
[blank] [blank] AMon3

But thanks for offering a solution

 

[Fazila]

This works but the problem would be a lot of duplication. I have found a report that will provide me with the information I am looking for but again thanks for the solution as always. I don't suppose you offer any excel courses or could recommend any?

Thanks again.

 

[Aladin Akyurek]

The formula works but there would be too many blanks in between i.e. given the example above the result would be

column G column H column I
[blank] [blank] AMon3

But thanks for offering a solution

Th formula does not create blanks at all, if that's what you mean.

 

[Fazila]

Th formula does not create blanks at all, if that's what you mean.

Not so much a blank but as the formula returns no value the cell is blank until it comes across a match. Sorry my words probably don't make much sense but the result I got was jtakw's formula was:

	F	G	H	I
			AMon:3
		AMon:2

<tbody>
</tbody>

but I would have to continue to drag the formula the original data is from B to BR which would mean the formula would have to go across a lot of columns to get the information I need.

 

[Aladin Akyurek]

Not so much a blank but as the formula returns no value the cell is blank until it comes across a match. Sorry my words probably don't make much sense but the result I got was jtakw's formula was:

	F	G	H	I
			AMon:3
		AMon:2

<tbody>
</tbody>

but I would have to continue to drag the formula the original data is from B to BR which would mean the formula would have to go across a lot of columns to get the information I need.

Are you wanting a vertical list perhaps?

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​		AMon1	AMon2	AMon3	ATues1		11a/En
2​	AJA	11a/Ma1	7b/Sc1	11a/En1	8b/Pe1		AMon2
3​	HGE	8c/Te1	11a/En2	8b/Re1	7a/En1		AMon3
4​
5​

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$E$1,SMALL(IF(ISNUMBER(SEARCH("|"&$G$1,"|"&$B$2:$E$3)),COLUMN($B$1:$E$1)-COLUMN($B$1)+1),ROWS($G$2:G2))),"")