How to Lookup between a starting word and finishing word
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...
=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.
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.