Basic V-lookup function

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
All,

I think this is a basic V-look up function, but I am not very familiar with it, so I am not 100% sure. I did some searches on here, but they seem to give me very specific explanations of it.

Here is the problem I have:

Cells E39:E47 and F:39-F47 are the beginning and end of a range.

Example:
E40=160% and F40= 174.9%
E41=150% and F41=159.9%

G39:G47 are points that correspond with the range of numbers from E39-E:47 and F39:F47.
H39:H47 is the payout

I am looking to get the payout from H39:h47 in cell L39.

Can someone help me with the vlookup statement or if there is an easier way to do this.

Thanks!

Eric
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
Ret model example.xls
ABCD
1%LevelAttainmentTotalPointsAdjustedPayout
2175.0%1549$1,600
3160.0%174.9%1416$1,300
4150.0%159.9%1328$1,100
5140.0%140.9%1239$950
6130.0%139.9%1151$700
7120.0%129.9%1062$550
8110.0%119.9%974$400
9100.0%109.9%885$250
10<100%885$0
Sheet3
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,531
Office Version
  1. 365
Platform
  1. Windows
ineedadedt

How does your explanation in your original post relate to the sample data provided? Please explain your requirements in terms of the sample data.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053

ADVERTISEMENT

Don't know from which column you want to pull your first argument - try one of these:
Book1
EFGHIJKL
38% Level Attainment???Total PointsAdjusted Payout% LevelAdj. Payout
391.751.111154916001.751600
401.61.74914161300
411.51.59913281100???Adj. Payout
421.41.40912399501.1111600
431.31.3991151700
441.21.2991062550Ttl PointsAdj. Payout
451.11.19997440015491600
4611.099885250
Sheet1


formula in L39 is: =VLOOKUP(K39,E39:H46,4,FALSE)
formula in L42 is: =VLOOKUP(K42,F39:H46,3,FALSE)
formula in L45 is: =VLOOKUP(K45,G39:H46,2,FALSE)
 

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
Sorry, I didn't match up the letters in the sample format. First time using it. :oops:

This is how it relates:
A2:A10 and B2:B10 are a range.

Example: A3/B3 is the range of 160%-174.9%

I want to be able to look at the range say 165% and it will return $1,100 or if it's easier look up the Total points (c2:c10).

Does that help at all?

thanks!
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
First, two minor corrections:
1) B7 below should be 149.9%, not 140.9%, as per your B5.
2) 165% will return $1,300, not $1,100

For these "TRUE" vlookup formulas to work, your list must be sorted in ASCENDING order.

The formula in G3 is: =VLOOKUP(F3,A2:D10,4,TRUE)
The formula in G6 is: =VLOOKUP(F6,C2:D10,2,TRUE)
Book1
ABCDEFG
1% Level AttainmentTotal PointsAdjusted Payout
2<100%885$0% LevelAdj. Payout
3100.0%109.9%885$250165.0%$1,300
4110.0%119.9%974$400
5120.0%129.9%1062$550Ttl PointsAdj. Payout
6130.0%139.9%1151$7001416$1,300
7140.0%149.9%1239$950
8150.0%159.9%1328$1,100
9160.0%174.9%1416$1,300
10175.0%1549$1,600
Sheet1
 

Forum statistics

Threads
1,141,094
Messages
5,704,304
Members
421,337
Latest member
DeuxMilleSangue

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top