Basic V-lookup function

[ineedadedt]

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

 

[orbea_adam]

Can you post some sample data?

 

[ineedadedt]

Ret model example.xls
	A	B	C	D
1	%LevelAttainment		TotalPoints	AdjustedPayout
2	175.0%		1549	$1,600
3	160.0%	174.9%	1416	$1,300
4	150.0%	159.9%	1328	$1,100
5	140.0%	140.9%	1239	$950
6	130.0%	139.9%	1151	$700
7	120.0%	129.9%	1062	$550
8	110.0%	119.9%	974	$400
9	100.0%	109.9%	885	$250
10	<100%		885	$0
Sheet3

 

[Peter_SSs]

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]

Don't know from which column you want to pull your first argument - try one of these:

Book1
	E	F	G	H	I	J	K	L
38	% Level Attainment	???	Total Points	Adjusted Payout			% Level	Adj. Payout
39	1.75	1.111	1549	1600			1.75	1600
40	1.6	1.749	1416	1300
41	1.5	1.599	1328	1100			???	Adj. Payout
42	1.4	1.409	1239	950			1.111	1600
43	1.3	1.399	1151	700
44	1.2	1.299	1062	550			Ttl Points	Adj. Payout
45	1.1	1.199	974	400			1549	1600
46	1	1.099	885	250
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]

Sorry, I didn't match up the letters in the sample format. First time using it.

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]

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
	A	B	C	D	E	F	G
1	% Level Attainment		Total Points	Adjusted Payout
2	<100%		885	$0		% Level	Adj. Payout
3	100.0%	109.9%	885	$250		165.0%	$1,300
4	110.0%	119.9%	974	$400
5	120.0%	129.9%	1062	$550		Ttl Points	Adj. Payout
6	130.0%	139.9%	1151	$700		1416	$1,300
7	140.0%	149.9%	1239	$950
8	150.0%	159.9%	1328	$1,100
9	160.0%	174.9%	1416	$1,300
10	175.0%		1549	$1,600
Sheet1

 

[ineedadedt]

Thank you so much!! It worked perfectly. ray: