How to Lookup between a starting word and finishing word

If you have a list of 890 rows that have a starting file name and ending filing name for example, a library indexing system. If you are looking for a formula (no VBA/macros) that if you type in a subject that falls between the starting word and ending word it returns the location of the searched file.

The example below is a small 'snip' of the whole table that goes from row 5 through row 898. The formula would be placed in the middle column where the cell content is red text is 10AD5.

Searched Name:	Piano Manufacturing
Crate:	10AD5
Start File	End File	Crate
Philippine Islands, Int. Trbls.	Physiology, Research	10AD4
Piano Exchange	Poland, Warsaw, Monuments & Memorials	10AD5
Poland, Warsaw, Penal Institutions	Powers	10AD6

Add a helper column in D with formula in D5: =(A5>=$B$1)&(B5>=$B$1) copied down (assumes your input is in B1) ... note: you can hide this column
Then in B2 use formula: =INDEX($C$5:$C$7,MATCH("*TRUE*",$D$5:$D$898,0)) adjusting ranges to suit.

Ather way without helper column would be...

=INDEX($C$5:$C$898,MATCH(TRUE,INDEX(($A$5:$A$898>=$B$1)+($B$5:$B$898>=$B$1)>0,0),0))

Obtained from the OzGrid Help Forum.

Solution posted by NBVC.