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

shina67

Board Regular
Joined
Sep 18, 2014
Messages
138
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>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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?
 
Upvote 0
Did you remember to press Ctrl+Shift+Enter, not just Enter? here's my sample data:


Excel 2010
BCDE
101/09/201402/09/201403/09/2014
2Andrewh
3Billh
4Charlesh
5Davids
6Edwards
7Freds
8George
9Henry
10Ian
11Johnh
12Keithhh
13Larryh
14Mikehh
15Normanh
16Oscarhh
17Peterh
18Quentins
19Roberth
20Stans
21Tomh
22Ulyssess
23Vernon
24Will
25Xerxes
26Yusuf
27Zacks
Holidays


and the results:


Excel 2010
A
21Andrew
22Bill
23Charles
24John
25Larry
26Norman
27Peter
28Robert
29Tom
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.
 
Upvote 0
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))),"")
 
Upvote 0
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))),"")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top