Need to match a value within a range

[holliday50]

I have 2 spreadsheets, one with a single 3-digit number, and the other with ranges. I'm trying to return a number from another column when the 3-digit exists within the range. For example....

590

580-600;603-604;609-612;614;616

And 1.90 in another column in the same spreadsheet as the range.

So in this example since 590 exists within the 580-600 range, I want to return 1.90. If this is not possible through an Excel formula, VB will work as an alternative. Thanks.

 

[Todd Bardoni]

Perhaps:-

Book2
	A	B	C	D	E
1	RngofVal	ReturnVal		Lookup	Formula
2	580	1.9		603	2
3	603	2
4	609	2.9
5	613	3.5
6	615	4
Sheet1

 

[holliday50]

The problem is that the ranges are all in one row. A VLOOKUP wouldn't work if your value is 581 within that range.

 

[Todd Bardoni]

The problem is that the ranges are all in one row. A VLOOKUP wouldn't work if your value is 581 within that range.

I just saw this...

What do you mean by 'the ranges are all in one row'?

 

[Peter_SSs]

holliday50

Also, what do you want to return if the lookup number is not in any of the ranges? eg 607