# is this an array problem?

#### bigbloo

##### New Member
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.

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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)

bigbloo said:
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)

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.

bigbloo said:
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
ABCDEFG
1
2510red17blue
31115white5.5red
41620blue12white
5
6
Sheet1

G2, copied down:

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

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!

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.

Replies
3
Views
179
Replies
9
Views
533
Replies
1
Views
358
Replies
5
Views
208
Replies
8
Views
372

1,203,744
Messages
6,057,123
Members
444,906
Latest member
NanaExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back