davideff
New Member
This title is hard to convey my needs. 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 if an associate has the room number searched and return associates name if so. Here is a general overview:
A B
-----------------------------------------------------------
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
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=
A B
-----------------------------------------------------------
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
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=