use of Vlookup for a range of values

[amanojp]

Hello friends..
I have an excel sheet which helps to calculate pip e thickness and shcedule. The schedule is selected based on thickness calculated for eg. On “refdt” sheet, when the pipe thknss is calculated as something in between 0.065 to 0.109, the schedule should be selected as “40”. For some value which is <= 0.147 but greater than 0.109 , the schedule selected should be “80”
The nested If function do not work for this as too many conditions are involved. How do we apply Vlookup to this data for searching in a range? Please advise.(sheet attached)

Thanks
Maoj

[NBVC]

Set up a table on the side.

Start the first column at 0 and list all the lower bounds for your lookups. In the adjacent column, list the corresponding Schedule numbers...

Then use formula like:

=LOOKUP(S5,$X$2:$Y$20)

where X2:Y20 is the table you created.

[amanojp]

Hi,

Thanks for your reply... Can you please demo this in the attached sheet.... meanwhile i will also try this from my end...

rgds

[amanojp]

Hi,

Another thing i forgot to mention here is that there are two conditions applicable ==( pls refer file attached)
---- When the pipe size selected on the earlier sheet "cal pg1" is say '1/2' (col L) ; the pipe thickness may be found to be calulated as say '0.089' (col N) , then the pipe schedule no. will be '40' (col M). Thus there will be these two conditions applicable i.e pipe size & wall thickness... How to address this condition.??

as you can see from col N, the 0.089 inch wall thickness could be applicable to Pipe size 1/8, or 1/4/or 1/2 or 3/4 i.e to multiple pipe sizes...

hope i am able to explain the problem correctly.

thanks.

[NBVC]

I am a bit confused now as to what you actually need here

Which exact cells do you need a formula for, and what are the input parameters for the formula you need?

[amanojp]

Please refer to attached sheet and explanation in 'H29'

hope this is clear.

many thanks...
manoj

[NBVC]

Does this work?

=INDEX(INDEX($L$5:$L$161,MATCH(E17,$K$5:$K$161,0)):$L$161,MATCH(TRUE,INDEX(INDEX($M$5:$M$161,MATCH(E17,$K$5:$K$161,0)):$M$161>=E28,0),0))

[amanojp]

Mind blowing !!! it worked.... thanks a lot.....

[NBVC]

Great! You are welcome.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save