Excel formula to find a number within a range of numbers and return a date

rosanna louise 0
Viewed 56

First time writing one of these, so I will try and explain what I'm trying to do in an understandable manner.

I'm trying to create a spreadsheet to track when a premium bond wins, and have it return the date that the winning premium bond was purchased. Because premium bonds cannot be purchased singularly, when you buy them your bond numbers are given to you a range (eg: 111AA111111- 111AA111210). As this is the data they provide, I've set my spreadsheet up with purchase date in column A, first number of the range in column B, and last number of the range in column C.

Sample image of the sheet with example bond ranges and purchase date:

enter image description here

Now on a separate sheet I've listed the wins, with win date in A, winning bond number in B, and prize amount in C. In D or E I would like to somehow get it to then give me from the first sheet the date of purchase that winning bond was from.

Sample image of the sheet with winning bond numbers and month they won:

enter image description here

I've done a lot of googling and found similar threads to do this, but none of them have been quite what I want and I've not yet been able to work out how to tweak them into doing it. These have used index and sumproduct, but I'm yet to get it to work.

Any help in the right direction is greatly appreciated. If this doesn't make sense let me know and I'll try and explain it better.

excel-formula excel
2 Answers

So, like this:

enter image description here

INDEX($B$3:$B$5,MATCH(C9,$C$3:$C$5,1))

Not added a constraint to limit to the upper value, but you could.

Solar Mike0

This answer is pretty much the same than @SolarMike's answer but based on a diferent point of view.

If the pattern is always like 111AA111111 where 1 is a numeric value and AA is a text value and unique then you can extract the text part from each id using function MID and search for it with INDEX and MATCH:

enter image description here

Formula is:

=INDEX($A$1:$A$3;MATCH("*"&MID(B9;4;2)&"*";$B$1:$B$3;0))

But for this to work each AA must be unique.

Foxfire And Burns And Burns0
Related