Report thread as spam
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
This question was started by Serge 7 years ago
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
This response was posted by Serge 7 years ago
Just a guess, Serge,
try changing FALSE to TRUE for the 4th param in the VLOOKUP
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
>
>
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
This response was posted by Max 7 years ago
Thanks for your reply.
Sorry Max,
It din't work.
Just a guess, Serge,
try changing FALSE to TRUE for the 4th param in the VLOOKUP
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
>
>
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
This response was posted by Serge 7 years ago
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
This response was posted by Pete_UK 7 years ago
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
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
This response was posted by Serge 7 years ago
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!
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
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
This response was posted by paul 7 years ago
Hello Paul
Thank you for clarifying that.
I did that and it works GREAT now.
Many thanks
Serge J
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!
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
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
This response was posted by Serge 7 years ago
Thanks Max,
First I did that to the wrong file. I made the change to the previous one,
and now it works GREAT.
Just a guess, Serge,
try changing FALSE to TRUE for the 4th param in the VLOOKUP
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
>
>
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
This response was posted by Serge 7 years ago
Glad you got that worked out, Serge !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
Thanks Max,
First I did that to the wrong file. I made the change to the previous one,
and now it works GREAT.
This response was posted by Max 7 years ago
Other excel threads