Want to fine tune VLOOKUP formula given last week
 

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
=0.875 1.75
<1.125 1.75
=1.125 2.00
<1.375 2.00
<=1.375 2.25
<1.625 2.25
=1.625 2.50
<1.875 2.50
=1.875 2.75
<2.125 2.75
=2.125 3.00
<2.375 3.00

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

Want to fine tune VLOOKUP formula given last week
 

"Serge" wrote:

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
=0.875 1.75
<1.125 1.75
=1.125 2.00
<1.375 2.00
=1.375 2.25 (made correction)
<1.625 2.25
=1.625 2.50
<1.875 2.50
=1.875 2.75
<2.125 2.75
=2.125 3.00
<2.375 3.00

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

Want to fine tune VLOOKUP formula given last week
 

Just a guess, Serge,
try changing FALSE to TRUE for the 4th param in the VLOOKUP

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Serge" wrote in message
...


"Serge" wrote:

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
=0.875 1.75
<1.125 1.75
=1.125 2.00
<1.375 2.00
=1.375 2.25 (made correction)
<1.625 2.25
=1.625 2.50
<1.875 2.50
=1.875 2.75
<2.125 2.75
=2.125 3.00
<2.375 3.00

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

Want to fine tune VLOOKUP formula given last week
 

Thanks for your reply.
Sorry Max,
It din't work.

"Max" wrote:

Just a guess, Serge,
try changing FALSE to TRUE for the 4th param in the VLOOKUP

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Serge" wrote in message
...


"Serge" wrote:

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
=0.875 1.75
<1.125 1.75
=1.125 2.00
<1.375 2.00
=1.375 2.25 (made correction)
<1.625 2.25
=1.625 2.50
<1.875 2.50
=1.875 2.75
<2.125 2.75
=2.125 3.00
<2.375 3.00

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

Want to fine tune VLOOKUP formula given last week
 

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

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

Want to fine tune VLOOKUP formula given last week
 

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

"Pete_UK" wrote:

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

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

Want to fine tune VLOOKUP formula given last week
 

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
--
paul
remove nospam for email addy!



"Serge" wrote:

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

"Pete_UK" wrote:

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

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

Want to fine tune VLOOKUP formula given last week
 

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

Many thanks

Serge J
"paul" wrote:

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
--
paul
remove nospam for email addy!



"Serge" wrote:

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

"Pete_UK" wrote:

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

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

Want to fine tune VLOOKUP formula given last week
 

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


"Max" wrote:

Just a guess, Serge,
try changing FALSE to TRUE for the 4th param in the VLOOKUP

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Serge" wrote in message
...


"Serge" wrote:

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
=0.875 1.75
<1.125 1.75
=1.125 2.00
<1.375 2.00
=1.375 2.25 (made correction)
<1.625 2.25
=1.625 2.50
<1.875 2.50
=1.875 2.75
<2.125 2.75
=2.125 3.00
<2.375 3.00

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

Want to fine tune VLOOKUP formula given last week
 

Glad you got that worked out, Serge <g !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Serge" wrote in message
...
Thanks Max,
First I did that to the wrong file. I made the change to the previous one,
and now it works GREAT.