Want to fine tune VLOOKUP formula given last week

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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...
 
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
 
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
 
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
 
Hello Paul
Thank you for clarifying that.
I did that and it works GREAT now.

Many thanks

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