is this an array problem?

[bigbloo]

My data is stored as follows:
Col A rows 1-3 are minimum values and Col B rows 1-3 are maximum values and Col C rows 1-3 are values to be returned depending on which range an input value falls in.

_____A ____B______C
1____ 5____10______red
2____11____15_____white
3____16____20_____blue

I want a stmt that says if a1<= 'valueX' <=b1, then c1 or if a2<='valueX' <=b2 then c2, or if a3<='valueX'<=b3, then c3.

My actual list of ranges a:b are quite long, there must be an array formula or some elegant if statement?
Thanks very much.

 

[nbrcrunch]

I'm not sure I follow you completely, but over the past few days this question about min & max range values has come up repeatedly.

You do not need both max and min, just one of the two columns. I usually use max.

Use VLOOKUP to find the value in the lookup range and then return the value in the next column.

For example, you run the Red, Blue & White dinner cruise lines that runs tours throughout greater Vancouver BC. And, as luck would have it, your three boat sizes for cruise parties are named Red, Blue & White.

The White boats carry passengers up to 15 in number
The Blue boats carry passengers up to 30 in number
The Red boats carry passengers up to 60 in number

In column A, rows 1 through 3 you enter
15
30
60

In column B, rows 1 through 3 you enter
White
Blue
Red

In cell A5 you enter the number of the guests for the party you are booking.

In cell A6 you enter the formula

=VLOOKUP(A5, A1:A3,2)

 

[Aladin Akyurek]

My data is stored as follows:
Col A rows 1-3 are minimum values and Col B rows 1-3 are maximum values and Col C rows 1-3 are values to be returned depending on which range an input value falls in.

_____A ____B______C
1____ 5____10______red
2____11____15_____white
3____16____20_____blue

I want a stmt that says if a1<= 'valueX' <=b1, then c1 or if a2<='valueX' <=b2 then c2, or if a3<='valueX'<=b3, then c3.

My actual list of ranges a:b are quite long, there must be an array formula or some elegant if statement?
Thanks very much.

If values in A:B both are in ascending order...

=LOOKUP(LookupValue,A:C)

 

[bigbloo]

thanks nbrcrunch, I think the range you meant was a1:b3, not a3.
but unless I'm missing something, that still doesn't solve my min/max range problem.
for instance,
if I have three ranges 1-5,6-10,11-15 if the entered value is in 1st range, return A, if it falls in range 2, then return B, if in range3, return C.
Thanks for any help.

 

[Aladin Akyurek]

thanks nbrcrunch, I think the range you meant was a1:b3, not a3.
but unless I'm missing something, that still doesn't solve my min/max range problem.
for instance,
if I have three ranges 1-5,6-10,11-15 if the entered value is in 1st range, return A, if it falls in range 2, then return B, if in range3, return C.
Thanks for any help.

Why change the example and provide one that can cause confusion?

Book18
	A	B	C	D	E	F	G
1
2	5	10	red			17	blue
3	11	15	white			5.5	red
4	16	20	blue			12	white
5
6
Sheet1

G2, copied down:

=LOOKUP(F2,$A$2:$C$4)

 

[bigbloo]

thanks Aladin - your formula had too few arguments, but when I added the ref of the value to return, it worked perfectly for my min/max ranges.
Thanks very much problem solved!

 

[bigbloo]

sorry Aladin, I thought restating problem differently might give better example of the range issue.
In any case your earlier sggestion really helped. Thanks again.