Here is the problem, I am given a number say 155 and want to find out which range the number falls in. For example there are ranges like 142-151,152-161, and 162-171. I need a function that tells me my number 155 belongs to the labeled range 152-161. Any suggestions? thanks
Lookup Number In Cells Holding 2 Numbers
-
-
-
Re: Search Between Range
hi and welcome to Ozgrid
Are you looking for a worksheet function or a VBA formula to your question
toe -
Re: Search Between Range
i was hoping for a worksheet function it probably isn't well explain but i'll give another quick example
Value 155
Ranges listed in column like
A B
142-151 1
152-161 2
162-171 3
etc....
The value will always fall within a range and i am looking to have a VLOOKUP function that looks up what range the number fall in and returns 2 in this case
hope that helps
thanks -
Re: Search Between Range
You should never store 2 numbers in the same.
However, to work-around your problem, you should split your 2 numbers. Either via Data>Text to Columns... or with a formula like below
=--LEFT(A1,FIND("-",A1)-1)
Then, use VLOOKUP like below
=VLOOKUP(155,MyTable,2)
Where MyTable is a [nr]*[/nr] and the left most column houses your lowest (left most) numbers. It must also be sorted in Ascending order
-
Re: Lookup Number In Cells Holding 2 Numbers
G'day Bott59,
Would you consider using [if]*[/if] eg if(and(x>152,x<161),2,0)?
Doing it this way means it is all done in formulas rather than vba. If VBA is required it can still be done by nested if statements.
Cheers,
GB
-
-
Re: Lookup Number In Cells Holding 2 Numbers
Could only have 8 IFs of course...
-
Re: Lookup Number In Cells Holding 2 Numbers
Hi,
=INDEX(B1:B3,MATCH(D1,INDEX(LEFT(A1:A3,3)+0,0,0)))
where D1 houses the value, A1:A3 houses the range.
HTH
-
Re: Lookup Number In Cells Holding 2 Numbers
Kris, I think you mean
=INDEX(A1:B3,MATCH(D1,INDEX(LEFT(A1:A3,3)+0,0)),2)
-
-
Re: Lookup Number In Cells Holding 2 Numbers
You left out 2 for the Column number
-
-
Re: Lookup Number In Cells Holding 2 Numbers
That's not a problem, since my index range is b1:b3.
-
Re: Lookup Number In Cells Holding 2 Numbers
Sorry, my bad. Missed that bit
-
Re: Lookup Number In Cells Holding 2 Numbers
this is another approach
=INDEX(A1:A5,MATCH(C1,VALUE(LEFT(A1:A5,FIND("-",A1:A5)-1)),1))
where the numbers are in range A1:A5 (chng range as necessary) and the value to be looked up is in cell C1
this is an array formula and needs to be confirmed by Ctrl+Shift+Enter
note : it is assumed there is no space between the first number and the "-"
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!