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

"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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>
>
> "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
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "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
> > >
> > >
>
>
>

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

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
>
>

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
> >
> >

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
> > >
> > >

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> > "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
> > >
> > >
>
>
>

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