Want to fine tune VLOOKUP formula given last week

S

Serge

The formula shown below was working fine.
But as you can see for example between 0.75 & 0.8125 there are possible
input that will give an error message.
=VLOOKUP(I9, INDIRECT(VLOOKUP(D9, $P$1:$Q$9,2)),2,FALSE)
Current table below:
".500 Dia. Bolt
GRIP INCHES" Length (inches)
0.7500 1.50
0.8125 1.50
0.8750 1.75
0.9375 1.75
1.0000 1.75
1.0625 1.75
1.1250 2.00
1.1875 2.00
1.2500 2.00
1.3125 2.00
1.3750 2.25
1.4375 2.25
1.5000 2.25
1.5625 2.25
Until I tried to modify the table to cover ranges that the first table did
not have.
now I get #N/A
New table below:
".500 Dia. Bolt
GRIP INCHES Length (inches)
=0.750 1.50 <0.875 1.50
<1.375 2.00
<=1.375 2.25
<1.625 2.25
<2.125 2.75
=2.125 3.00
Click to expand...
<2.375 3.00

Can someone help with my problem please.
Many thanks in advance
 
S

Serge

Serge said:
The formula shown below was working fine.
But as you can see for example between 0.75 & 0.8125 there are possible
input that will give an error message.
=VLOOKUP(I9, INDIRECT(VLOOKUP(D9, $P$1:$Q$9,2)),2,FALSE)
Current table below:
".500 Dia. Bolt
GRIP INCHES" Length (inches)
0.7500 1.50
0.8125 1.50
0.8750 1.75
0.9375 1.75
1.0000 1.75
1.0625 1.75
1.1250 2.00
1.1875 2.00
1.2500 2.00
1.3125 2.00
1.3750 2.25
1.4375 2.25
1.5000 2.25
1.5625 2.25
Until I tried to modify the table to cover ranges that the first table did
not have.
now I get #N/A
New table below:
".500 Dia. Bolt
GRIP INCHES Length (inches)
<2.375 3.00

Can someone help with my problem please.
Many thanks in advance
Click to expand...
 
P

Pete_UK

Yes, but you have also modified the table so that where you had, for
example,

0.7500 1.50 before, you now have
=0.750 1.50
Click to expand...

This is a totally different value to Excel (i.e. text, rather than
number), so your vlookup formula won't work.

Max's suggestion will allow your formula to "fill in the gaps", as it
were (well, with the original table), so that if a number is input
which is not in the table then the number below it will be taken
instead. Is this what you want?

Pete
 
S

Serge

Hello Pete,
I needed to modify the table because the input I9 can be anywhere between
the sizes shown on the grip range,thus retreiving a bolt length, when the
input in I9 get lower or higher then it should retreive a different bolt
length.
Hope this help. If yuo are willing I would send you my spread sheet.
Many thanks Pete.
Serge
 
P

paul

what pete and max mean is to use your OLD table but change the fasle to
true,this means if an exact match is not found, the next largest value that
is less than lookup_value is returned
 
S

Serge

Hello Paul
Thank you for clarifying that.
I did that and it works GREAT now.

Many thanks

Serge J
 
S

Serge

Thanks Max,
First I did that to the wrong file. I made the change to the previous one,
and now it works GREAT.
 
Top