How to Lookup between a starting word and finishing word

< Back to Search results

 Category: [Excel]  Demo Available 

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



Obtained from the OzGrid Help Forum.

Solution posted by NBVC.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to use bottom up lookup
How to use VLOOKUP (example)
How to create a formula for multi criteria lookup with dates
How to use advanced lookup: Multiple criteria when looking up values in a table
How to use the VLOOKUP formula
How to use IF and VLOOKUP formulas together
How to maintain the VLOOKUP font format
How to insert VLOOKUP into cell with variable array


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)