Find a word within a range and return contents of a different cell in excel

[shina67]

Hi,
Hope someone can help with the problem I have within a workbook.
I need a formula to insert into A21 through to A53 that will find the date from A1, in worksheet 'Holidays' row 1 then search that column for "h" or "hh" and return the name from column B. Also I need a similar formula to insert into B21 through to B53 that will find the date from A1, in worksheet 'Holidays' row 1 then search that column for "s" and return the name from column B. Hope this makes sense. I can't insert images to help with my explanation, sorry.

<tbody>
</tbody>

 

[Andrew Poulsom]

Example for h, in A21 confirmed with Ctrl+Shift+Enter and copied down:

=IFERROR(INDEX(Holidays!B:B,SMALL(IF(INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE))="h",ROW(Holidays!B:B)),ROWS(A$21:A21))),"")

Try to avoid referencing entire columns.

 

[shina67]

Thanks for your reply Andrew Poulsom. This doesn't seem to work for what I am requiring. It returns the same result everytime regardless of what is inputted in the 'Holidays' worksheet. Could you help if I could forward the workbook to you?

 

[Andrew Poulsom]

Did you remember to press Ctrl+Shift+Enter, not just Enter? here's my sample data:

Excel 2010
	B	C	D	E
1		01/09/2014	02/09/2014	03/09/2014
2	Andrew		h
3	Bill		h
4	Charles		h
5	David		s
6	Edward		s
7	Fred		s
8	George
9	Henry
10	Ian
11	John		h
12	Keith		hh
13	Larry		h
14	Mike		hh
15	Norman		h
16	Oscar		hh
17	Peter		h
18	Quentin		s
19	Robert		h
20	Stan		s
21	Tom		h
22	Ulysses		s
23	Vernon
24	Will
25	Xerxes
26	Yusuf
27	Zack		s
Holidays

and the results:

Excel 2010
	A
21	Andrew
22	Bill
23	Charles
24	John
25	Larry
26	Norman
27	Peter
28	Robert
29	Tom
Sheet1

Cell Formulas
Range	Formula
A21	{=IFERROR(INDEX(Holidays!B:B,SMALL(IF(INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE))="h",ROW(Holidays!B:B)),ROWS(A$21:A21))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shina67]

Thanks Andrew that seems to work fine if only a "h" or "s" is entered. I need a "hh" entry and "h" to be returned in the same result.

 

[Andrew Poulsom]

I assumed that you would be able to adjust the formula to suit:

=IFERROR(INDEX(Holidays!B:B,SMALL(IF((INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE))="h")+(INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE))="hh"),ROW(Holidays!B:B)),ROWS(A$21:A21))),"")

 

[shina67]

Thank you very much for your help

 

[shina67]

Andrew the formula worked fantastic that you helped with, however, when I opened the workbook on my work pc I realised that it is office 2003 and this formula won't work in 2003. Is it possible that you could help with a formula that will work with excel 2003. Thanks in advance. The original formula is below.

=IFERROR(INDEX(Holidays!B:B,SMALL(IF((INDEX(Holidays!E:G,0,MATCH(A$1,Holidays!E$1:G$1,FALSE))="h")+(INDEX(Holidays!E:G,0,MATCH(A$1,Holidays!E$1:G$1,FALSE))="hh"),ROW(Holidays!B:B)),ROWS(A$36:A36))),"")

 

[Andrew Poulsom]

Try:

=IF(SUM(COUNTIF(INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE)),{"h","hh"}))>=ROWS(A$21:A21),INDEX(Holidays!B:B,SMALL(IF((INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE))="h")+(INDEX(Holidays!C:E,0,MATCH(A$1,Holidays!C$1:E$1,FALSE))="hh"),ROW(Holidays!B:B)),ROWS(A$21:A21))),"")

confirmed with Ctrl+Shift+Enter and copied down.

 

[shina67]

Thanks again Andrew works a treat and when copied down. Don't know if I am being thick or just have a mind blank but what part of the formula do I have to change to copy across?