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

#### shina67

##### Board Regular
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

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

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?

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.

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.

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))),"")

Thank you very much for your help

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))),"")

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.

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?

Replies
5
Views
237
Replies
3
Views
170
Replies
7
Views
388
Replies
4
Views
520
Replies
1
Views
185

1,221,423
Messages
6,159,821
Members
451,591
Latest member
j0eyjedi

### 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.

### Which adblocker are you using?

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

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