Excel Formula - lookup number in a range stored as text

Question

I have a spreadsheet that uses named ranges to maintain dynamic control. I have a search box where user inputs text (can be number, but cell is formatted as general). When user inputs a number the LOOKUP() formula will search a specified range for that number and return whatever data. Here is a general overview:

      A     B                                        C
  ----------------------------------------------------
1 |Search: 232 <- user input (Named rnge: SearchTerm)|
  ----------------------------------------------------
2 |  Assc: ....                                      |
  ----------------------------------------------------
3 | Phone: ...                                       |
  ----------------------------------------------------

And the LOOKUP() range, we'll call it Table1, is as follows:

  -------------------------------------------
 | Associate   |   Rooms     | Phone Number |
 --------------------------------------------
 | John Doe    |   110-130   | xxx-xxx-xxx  |
 --------------------------------------------
 | Jane Doe    |   131-150   | xxx-xxx-xxx  |
 --------------------------------------------
 | Etc...                                   |
 --------------------------------------------

I have successfully managed to find a cell using LOOKUP(), but not the a cell in a dynamic range. Also my current formula is using a lot of IF()/AND()/OR() statements so it's getting longer than necessary. The way I am currently seeing if the user input value is between the two numbers is not pretty, I've been slicing the Room value using LEFT() to get the first number and RIGHT() to get the right most number. I've even played around using MAX() in conjunction with other functions like SUBSTITUTE() etc but my functions are WAY too long and often lead to errors because it's almost 4 lines long. Ouch...I know. Is there an easier way to see if an associate is assigned the room entered in the search box, without VBA, as opposed to my long IF/AND/OR statement formulas? Specifically, looking for: B2

This is what I've come up with that, like I said, finds a solution for one specific cell (C5 is Table1's first row under Rooms column), but I need it to find where the number fits in autonomously:

=IFNA(IF(AND(SearchTerm>=NUMBERVALUE(LEFT(C5,3)),SearchTerm<=NUMBERVALUE(RIGHT(C5,3))),OFFSET(C5,0,-1,1,1),LOOKUP(1E+307,FIND(" "&SearchTerm&","," "&Table1[Misc Rooms]&","),Table1[Associate])),"NO MATCHES FOUND")

starting at LOOKUP the formula refers to data unrelated to question. Formula checks if searched room is within associates area (Column 'Rooms'), if not, looks to see if its a miscellaneously assigned room normally not in the associates area

TL;DR Whats the most efficient formula to see if a number falls between a hyphenated number (entered as text) and return a column next to it using LOOKUP()/VLOOKUP()/INDEX()/MATCH() etc, anything not VBA really. Looking for B2=

Answer 1

Consider using just one number in the Room column. Or use first room and last room as two different columns. Then you can use an approximate match, which will return a value for a match that is less than or equal to the search value. Take a look at the help for Vlookup or Match with a 1 or TRUE as the last parameter.

The table must be sorted by first Room in ascending order.

=INDEX(Table1[Associate],MATCH(A8,Table1[first Room],1))

Edit: If splitting the column is not an option, the consider the following:

Option 2 formula: This option will need the data to be sorted ascending by the first room number and there should not be any gaps in the room number. Example of the result is in cell E3, referring to the number entered in D3:

=INDEX(Table1[name],MATCH(D3,INDEX(LEFT(Table1[room],FIND("-",Table1[room])-1),0)+0,1))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Option 3 formula: With this formula the data in the Room column is taken as is. Order is not important. The only condition is that there is only a single dash separating the numbers, no other characters. The formula is in use in cell E4, referring to the number entered in D4. Not an array formula, just hit Enter normally:

=LOOKUP(2,1/((D4>=LEFT(Table1[room],FIND("-",Table1[room])-1)+0)*(D4<=MID(Table1[room],FIND("-",Table1[room])+1,99)+0)),Table1[name])

Edit 2: Explanation of Option 3

LOOKUP(2,1/(comparison),ResultRange) -- The comparison resolves to a list of TRUE or FALSE values. Dividing 1 by the comparison results will yield a 1 for TRUE and a #DIV/0 error for FALSE values. The lookup for 2 will find the last 1 in the list of results and return its equivalent from the same position in the ResultRange.

Combining two comparisons with a logical AND can be achieved by multiplying the comparison results, which will resolve into a single list of TRUE/FALSE values, i.e. LOOKUP(2,1/((comparison1)*(comparison2)),ResultRange)

The column room contains text. Splicing out the number still returns the number as text. The user-entered search term is a real number. These two data types will never match, so we need to convert either the entered number to text or convert the spliced out text to a real number. Since we are checking for a number being greater or smaller than something, we need both data types as numbers. So, we need to convert the spliced out text into a number. This can be done by applying a calculation to the text, for example adding a zero, hence the +0. You could also use multiplication with 1 if you are sure all your numbers are positive.