Find Between

[GB]

Hi,

I know this should be simple but for some reason it is not. I have a range of data. In column A I have numbers. To the right of each number in column A I have a range of numbers that are progressively bigger than each number in column A. I want to find a number in column A then in that row find the number that is between two values. See attached.


https://dl.dropbox.com/u/60464004/Find%20Number%20in%20a%20row.xlsx


Appreciate your help.

regards

GB

 

[NARAYANK991]

Hi GB ,


I am not sure this will give the precise result you are looking for , since it does not cater to two limits :


=LOOKUP(N2,INDEX(Data_Range,L1,0))


where Data_Range is A834.


Narayan

 

[GB]

Hi Narayan,

thanks but it doesn't work correctly as your formula ignores the lower limit (cell L2). Any other suggestions?


regards

GB

 

[NARAYANK991]

Hi GB ,


What should be displayed if no value falls between the limits selected ?


What should be displayed if more than one value falls between the limits selected ?


Narayan

 

[SirJB7]

Hi, GB!


Try this:

=INDICE(A834;COINCIDIR(L1;A8:A34;1);COINCIDIR(L2;INDIRECTO("B"&COINCIDIR(L1;A8:A34;1)+7&""&COINCIDIR(L1;A8:A34;1)+7))+1) -----> in english: =INDEX(A834,MATCH(L1,A8:A34,1),MATCH(L2,INDIRECT("B"&MATCH(L1,A8:A34,1)+7&""&MATCH(L1,A8:A34,1)+7))+1)


Regards!

 

[GB]

Hi Narayan,

to answer your questions...

- blank

- won't be possible to have multiple results in my case, but a good question.


Hi SirJB7,

I will try your formula (I am sure it works), then spend some time trying to work it out.


Thanks to you both for quick response. I really appreciate it.

regards

GB

 

[SirJB7]

Hi, GB!


Thanks for your feedback and for your kind words too. Let us know about your progress.


Regards!


PS: I don't like too much the method of 2 values (from/to, L2/N2) that the user might enter. There should be validations of N2>L2 (at least) and then what if there is more than 1 value between them? Note that in the proposed formula, I don't (is it necessary?) use N2 value.

 

[GB]

Hi SirJB7 & Narayan,

here's what I came up with that works for me.


=IF(LOOKUP(N2,INDEX(A834,L1,0))>L2,LOOKUP(N2,INDEX(A834,L1,0)),"")


Thanks again, you both got me thinking.


regards

GB

 

[NARAYANK991]

Hi GB ,


Good for you. Thanks for the feedback.


Narayan

 

[SirJB7]

Hi, GB!

Glad you solved it. Thanks for your feedback and for sharing your solution. Welcome back whenever needed or wanted.

Regards!

PS: Did we do that? I didn't do it (Bart's dixit). NARAYANK991 was, for sure...