Finding a certain row from rows of intervals?

[Sinuouser]

Hello!

Got a question, mostly curious, and doubting this could be done withou
Macros or similiar, but hey, I'll give it a go here, someone might kno


Problem:

Say you have two columns with a number of rows, each row contains a
interval in the form of "min-max", and you want to find the value t
the right of the correct interval, like so;

A B
1 1-7 1
2 8-25 2
3 26-29 3
4 30-40 4
5 41-57 5

Enter Number: [A number that you enter]
Number Returned: [Calculates corresponding number from the interval]

Now, if you enter number 34, it should return "4" (From the 30-4
interval)

I'm mostly curious to if this can be solved without using macros, bee
trying to think of different functions to use, but I'm fairly blu
regarding excel functions, mostly been using basic calculations an
table-building functions before.

Hope anyone knows a smart way of doing this

/Rickar

 

[Don Guillett]

you could use a helper column
=LEFT(H3,LEN(H3)-FIND("-",H3))
and then use
=MATCH(TEXT(J1,"@"),J2:J32)

 

[Sinuouser]

you could use a helper column
=LEFT(H3,LEN(H3)-FIND("-",H3))
and then use
=MATCH(TEXT(J1,"@"),J2:J32)

Hmm, when you say > you could use a helper column

=LEFT(H3,LEN(H3)-FIND("-",H3))

I'm guessing you mean make a column of the numbers on the left side o
the "-", making it:
=LEFT(H3,FIND("-",H3)-1)
?

With the Match I'm not sure how it works, tried how you typed it down
making J1 the cell where you enter the number and J2:J32 the helpe
column, but it returns the number corresponding to the range below th
one entered..

Also tried making the helper column the right side of the "-" char
with
=RIGHT(H3,LEN(H3)-FIND("-",H3))

And using MATCH as above, that returns the right number except whe
reaching the MAX number of each range..


Code
-------------------
A B
1 1-7 1
2 8-25 2
3 26-29 3
4 30-40 4
5 41-57
-------------------


When using number left of "-" here, and enter say number 6, it return
"2", 28 returns "4" etc.

When using number right of "-", 6 returns "1", 7 returns "2", for som
reason...

I have no idea what the "@" in your TEXT as format means... can someon
please explain it to me?

I'm thinking that I can just give the helper column the value of th
right number -1 and it should work however.

So, thanks for your help, set me on the right track

/Rickar

 

[Don Guillett]

that would be the easiest unless you change the values

I'm thinking that I can just give the helper column the value of the
right number -1 and it should work however.

 

[Ragdyer]

You could try this:

With your data list in A1:B5 (as posted),

Number to lookup is entered into C1,

Helper column is D, with this formula entered into D1:

=--LEFT(A1,FIND("-",A1)-1)

And copied down.

Use this for your result:

=INDEX(B1:B5,MATCH(C1,D15))

 

[Harlan Grove]

Sinuouser wrote...
....

Say you have two columns with a number of rows, each row contains an
interval in the form of "min-max", and you want to find the value to
the right of the correct interval, like so;

A B
1 1-7 1
2 8-25 2
3 26-29 3
4 30-40 4
5 41-57 5 ....
Now, if you enter number 34, it should return "4" (From the 30-40
interval)

....

This is not an efficient way to do things in spreadsheets, as you may
be discovering. Since most of the others have already suggested using
another column, you should realize it would have been better to start
off with 3 columns, the first containing the lower bounds of your
intervals, the second containing the (unnecessary) upper bounds of your
intervals, and the third containing your column B values.

However, you can use your table as above without having to use a third
column of cells to pull the interval lower bounds by using the
following formula.

=LOOKUP(34,--LEFT(A1:A5,FIND("-",A1:A5)-1),B1:B5)

 

[Sinuouser]

This is not an efficient way to do things in spreadsheets, as you may
be discovering. Since most of the others have already suggested using
another column, you should realize it would have been better to start
off with 3 columns, the first containing the lower bounds of your
intervals, the second containing the (unnecessary) upper bounds of
your
intervals, and the third containing your column B values.

However, you can use your table as above without having to use a third
column of cells to pull the interval lower bounds by using the
following formula.

=LOOKUP(34,--LEFT(A1:A5,FIND("-",A1:A5)-1),B1:B5)

Yeah, I know, it isn't mainly for calculations, but for printing and
viewing, that's why I have it in that format..., usually I do ranges
like that in a 3 column list.

And Yes! That worked! Finally

Thanks everyone for all the help!

Just a quick question now if I may, what does the "--" mean before the
"LEFT" in your solution?
Might as well learn as much as I can from this

/Rickard

 

[Harlan Grove]

Sinuouser wrote...
....

....
Just a quick question now if I may, what does the "--" mean before the
"LEFT" in your solution?

....

LOOKUP and similar functions are picky about data types. Numbers like
34 don't match text like "34". The -- before the LEFT call transforms
LEFT's text result into numbers. The rightmost - converts text like "1"
into the number -1, and the leftmost - changes the signs back to
positive.

 

[Sinuouser]

ohh, as simple as that, yeah, that was probably the problem before, that
it treated it as text and numbers and therefore gave errenous feedback
at times.

Thanks so much, now I know

/Rickard