VLookup with ranges

[Thorxes]

I have a 3 column table that goes:

FROM: /// TO: /// Value
0 /// 25,000 /// x
25,001 /// 50,000 /// y
50,001 /// 75,000 /// z

etc etc.

I then have another table ( 2 columns) with the first column being inputed values from another worksheet. This inputted number will fall between one of the ranges. The second column needs to have the appropriate VALUE from the above, 3 column table.

How do I go about making a Vlookup that can find ranges between 2 columns?
So if I have a value of 30,000, it will return "y".

[shg]

The second column to the table is redundant; the top of each range is the bottom of the next.

[Thorxes]

so should I be using the Vlookup function to check for the range, or sometype of loop?

[franciz]

why don't you attached your file with the expected result leaving out confidential info.
This would help us in assisting you

[shg]

Just a VLOOKUP:

Please Login or Register  to view this content.

[martindwilson]

turn the ranges upside down so it looks like this
cola///col b///col c
50001// 75000// z
25001// 50000// y
0// 25000 // x
then use
=INDEX(B1:C3,MATCH(F1,B1:B3,-1),2) so if you put 25001 in f1
it fil finf yhr closest highest match in col c and return Y

[Thorxes]

If you go to the loss projection factors worksheet.

At the top you'll see 2006 Q! # Claims. To the right of that needs to be a Credibility weight which is pulled from the table below.

You get the appropriate weight based on the number in the 2006 Q1 #Claims column.

[shg]

In G7 and copy down, =VLOOKUP(F7, $F$18:$H$28, 3)

[Thorxes]

Awesome. I didn't know that the vlookup would naturally look to see if the given value would be inbetween the values in a table.

<3

[shg]

Help is your Friend.

[franciz]

you may try in G7 and copy down

=VLOOKUP(F7,$F$18:$H$28,3,1)

hope this help

[Thorxes]

Oh one last thing. I saw a co worker use a macro made in VBA to print all certain sheets within a workbook? If I wanted to have a macro that printed out all of the worksheets except the "InputData" worksheet, how would I do that in VBA?

[franciz]

Shg, I didn't noticed that you have provided a solution.

Thorxes, Vlookup can return an exact match or approximate match depeding on the
4th criteria you input

check this out from Help

[Thorxes]

Interesting, SHG's solution didn't include a 4th criteria.

Any advice on the print macro?

[daddylonglegs]

Interesting, SHG's solution didn't include a 4th criteria.

If the 4th argument of VLOOKUP is omitted then it's asssumed to be 1 or TRUE. As shg says, "Help is your friend"....

You could also use a LOOKUP formula which always works on the principle of "closest match", i.e.

=LOOKUP(F7,$F$18:$H$28)