Vlookup Formula Help

[Utradeshow]

Hi All,

I have this problem I'm trying to get a vlookup formula for. Please see the example below. I tried to manipulate a previous formula I had, but there was too man changes to consider.

If mileage and discount are entered, the result is based on Vlookup and mileage <>300

Excel Workbook
	T	U	V	W	X	Y	Z
129	**	**	**	**	**		>300
130	**	**	**	**	0%	75.0%	71.0%
131	**	**	**	**	33%	75.0%	71.0%
132	**	**	**	**	34%	75.0%	71.0%
133	Mileage	Discount	Comm.	**	35%	75.0%	71.0%
134	1560	48.0%	76.5%	**	36%	75.0%	71.0%
135	**	**	**	**	37%	75.0%	71.0%
136	**	**	**	**	38%	75.0%	71.0%
137	**	**	**	**	39%	75.0%	71.0%
138	**	**	**	**	40%	75.0%	71.0%
139	**	**	**	**	41%	76.0%	72.0%
140	**	**	**	**	42%	77.0%	73.0%
141	**	**	**	**	43%	78.0%	74.0%
142	**	**	**	**	44%	79.0%	75.0%
143	**	**	**	**	45%	79.5%	75.5%
144	**	**	**	**	46%	79.5%	75.5%
145	**	**	**	**	47%	80.0%	76.0%
146	**	**	**	**	48%	80.5%	76.5%
147	**	**	**	**	49%	81.0%	77.0%
148	**	**	**	**	50%	81.5%	77.5%
Dim IN

Thank you, Dan

 

[Weazel]

maybe someone has a more elegant way of doing it but this seems to work

Excel 2012

	T	U	V	W	X	Y	Z
129						<300	>300
130					0%	75.00%	71.00%
131					33%	75.00%	71.00%
132					34%	75.00%	71.00%
133	Mileage	Discount	Comm.		35%	75.00%	71.00%
134	1560	48.00%	76.5%		36%	75.00%	71.00%
135					37%	75.00%	71.00%
136					38%	75.00%	71.00%
137					39%	75.00%	71.00%
138					40%	75.00%	71.00%
139					41%	76.00%	72.00%
140					42%	77.00%	73.00%
141					43%	78.00%	74.00%
142					44%	79.00%	75.00%
143					45%	79.50%	75.50%
144					46%	79.50%	75.50%
145					47%	80.00%	76.00%
146					48%	80.50%	76.50%
147					49%	81.00%	77.00%
148					50%	81.50%	77.50%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula V134 =IF(T134<300,LOOKUP(U134,X130:Y148),LOOKUP(U134,X130:Z148)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Skybluekid]

Another option would be:

=IF(T134<300,INDEX($Y$130:$Y$148,MATCH(U134,$X$130:$X$148,0),INDEX($Z$130:$Z$148,MATCH(U134,$X$130:$X$148,0)

 

[markmzz]

Maybe this:

In C134

=VLOOKUP($B134,$E$130:$G$148,2+($A134>300))

Markmzz