Performing a VLOOKUP with multiple ranges that are not joined together

[Carkcark]

Hi all,

Still a bit of a novice at Excel wizardy, but I have a range of serial numbers that do not touch together. My aim is to have a cell that I input a serial number into and I want another cell via a formula most likely via a vlookup (unless there is a better way that you clever people can suggest) that will identify if the serial number I have entered falls within one of the many different ranges.

These are the ranges I am using. The way I would like it to work is that I type in to a cell "ST84210", the cell then works down the 3rd column and stops at ST84209 and then checks to see if "ST84210" is between ST84209 and ST84218.

What formula should I be using? Any templates would be greatly appreciated as my google searching hasn't led me to the solution.

Range:	from	ST82307	to	ST82309
Range:	from	ST82335	to	ST82378
Range:	from	ST82385	to	ST82497
Range:	from	ST82499	to	ST82506
Range:	from	ST82550	to	ST82606
Range:	from	ST82617	to	ST82626
Range:	from	ST83896	to	ST83905
Range:	from	ST84209	to	ST84218
Range:	from	ST84224	to	ST84236
Range:	from	ST86138	to	ST86138

 

[cooper645]

I put my search value in cell H1 and your data in cell A1:E10.

I amended Column A to Read; Range: 1, Range: 2, Range:3 and so on and used this as the return column. I assume there may be another with a product name or some such unique identifier

In cell H2 I used the following:

=IF(XLOOKUP(H1,C1:C10,A1:A10,"",-1,1)=XLOOKUP(H1,E1:E10,A1:A10,"",1,1),XLOOKUP(H1,C1:C10,A1:A10,"",-1,1),"Not in range")