Lookup Value if number falls in range in same row

[fadzie21]

Happy new year. Been trying to work this out but can't seem to be able to find a solution. I have 2 sheets, and in sheet1 is my source data with for columns. Column A contains ticket numbers and can sometimes be grouped but the first 5 digits will always remain constant for that particular group. columns B & C are basic formulas to try and split the group so I get the first and last ticket number in each group and column D is the team the tickets belong to.

The tricky bit for me now is I get from a different source the same ticket numbers but in this one (Sheet2) the ticket numbers come split already and in individual rows but with no team names. What am trying to do is to be able to fill in the team names in sheet2 from the data in sheet1 eg Ticket number 900992897 will fall in the range 900992897-2899 and so should show team as Adventure. Hope this makes sense. Thanks in advance.


Sheet1 Sheet2
Column A Column B Column C Column D Column A Column B
Ticket Nos Start End Team Name Ticket Nos Team Name
900991547-1549 900991547 900991549 Adventure 900992897
900992356-2358 900992356 900992358 Admirals 900993578
900992569-2573 900992569 900992573 Dynamo 900994522
900992897-2899 900992897 900992899 Adventure 900995781
900993578-3579 900993578 900993579 Adventure 900992357
900994520-4522 900994520 900994522 Dynamo 900991548
900994589-4593 900994589 900994593 Captains 900992358
900995780-5785 900995780 900995785 Dynamo 900992569
900996897-6898 900996897 900996898 Admirals 900992573
900992898
900992899
900992570
900992571
900992572
900993579
900991549

[xladept]

Hi Fadzie,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[fadzie21]

Hi xladept

Thanks for the response. I have attached a sample of what am trying to do. Please note the ticket group can be more than 2 tickets. just noticed my sample on has max of 2 for those that are grouped.

thanks