Find if values are in a range (between val 1 and between val2), and get data

[Lolo]

Hello,

I have a basic problem, but don't manage to find the solution...
I have a list of several values ( each value is composed of 2 parts: text prefix (4 chars) and then 6 digits, for example ABCD123456

In another second list, I have ranges for example:
START END VALUE
ABCD123400 - ABCD123500 30000
ABCD123501 - ABCD123599 40000

I just want to know for each value of the 1st list, if the value is in a range of a second list, and get the corresponding value

For example : ABCD123456 is in the 1st (line) range, and I would get the value 30000

I'm able to know if each value of the 1st list match within a range of the second list (with COUNTIFS formula), but, I m not able to get the row concerned and the linked value...
I have tried EQUIV, VLOOKUP (Approximate search), without any success...

Hope you can help me...
Thank you !

NB: Keep in mind that 1st list could have thousand values, and the second one hundred values, so a complex formula

 

[Chihiro]

With your example you can use below...

=IFERROR(INDEX(Tableau1[START],MATCH(1,INDEX((RIGHT(Tableau1[START],6)<=RIGHT([@TC],6))*(RIGHT(Tableau1[END],6)>=RIGHT([@TC],6)),),0)),"No Serie")

... to find Serie Start.

Then use another index,match to find serie value.

If TC can be between multiple serie, you will need additional condition.

 

[Lolo]

Thank you for this !!
I have also found another way by using approximate VLOOKUP on the start range (needs to be sorted). Then I use a classic VLOOKUP to get the linked END RANGE. then I create another formula to check if the TC value is <= END RANGE found. If yes the TC value is in the range, so I can get the value with another classic VLOOKUP. So more steps (4), but advantage is that is will work fine even on big list. Not sure your shorter solution will be so efficient in case of big list. I have to check it.