Formula Help: Find cell in a range that contains a number - search values are hyphenated numbers

[davideff]

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=

 

[Hui]

Davideff

Firstly, Welcome to the Chandoo.org Forums

Can you please attach a sample file with say a Dozen records and an example of what your trying to achieve?

 

[bosco_yip]

Try,

1] In B3, enter formula :

=LOOKUP(B$2,0+LEFT(G$3:G$100,FIND("-",G$3:G$100)-1),F$3:F$100)

2] In B4, enter formula :

=LOOKUP(B$2,0+LEFT(G$3:G$100,FIND("-",G$3:G$100)-1),H$3:H$100)

Regards

 

[davideff]

Davideff

Firstly, Welcome to the Chandoo.org Forums

Can you please attach a sample file with say a Dozen records and an example of what your trying to achieve?

Oh sorry, sure! I love you member title lol Excel Ninja

 

[davideff]

View attachment 36876

Try,

1] In B3, enter formula :

=LOOKUP(B$2,0+LEFT(G$3:G$100,FIND("-",G$3:G$100)-1),F$3:F$100)

2] In B4, enter formula :

=LOOKUP(B$2,0+LEFT(G$3:G$100,FIND("-",G$3:G$100)-1),H$3:H$100)

Regards

Thank you! You are the best. Gez, this is the closest thing I've seen to a solid solution. There is an issue however, some associates won't have rooms for the day at all, so their respective Room row will be empty and B3 in your example will always be the person below the number given. So for your example if Jane Doe doesn't have any rooms assigned and the search number is 105 but peters numbers stay 121-140, the associate pop up will be John Doe even though his max is 100. Seriously, thank you though for the quick response

 

[davideff]

@bosco_yip @Hui I attached a working example. I'm going to keep working on it based on what bosco delivered. You don't have to combine both the formulas into one. I can handle that, I hope. I actually want to learn how to do it individually first. See attachment and let me know if it makes more sense? Thanks for the quick responses!

 

[AVK]

Dear Davideff, Please find attach revised file.

 

[bosco_yip]

Please find revised file, should the results meet with your expected?

p.s. I combine 2 sheets of table into one sheet for easy checking.

Regards
Bosco

 

[davideff]

View attachment 36899

Please find revised file, should the results meet with your expected?

p.s. I combine 2 sheets of table into one sheet for easy checking.

Regards
Bosco

Thank you for everything and all your help! This does the trick. You are the best!