VLOOKUP with ranges

[ORoxo]

Hey, guys,
I'm aware its possible to use vlookup to search in a range. However, let's say you only have the maximum limit. For the purpose of example a have column A with amount and column B with percentage.
Column A figures represent salaries:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]632[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]645[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]683[/TD]
[TD]5,7%[/TD]
[/TR]
[TR]
[TD]736[/TD]
[TD]7,5%[/TD]
[/TR]
</tbody>[/TABLE]

How does this work? Basically, if someone gets a salary UNTIL 736, it will pay 7,5%. In other words, 736 is the upper limit of the range.

If I use vlookup with TRUE as last statement, it will interpret 736 as the lower limit and, therefore, for a salary of 730, for instance, will return 5,7% when it should return 7,5%.

How can I solve this?

Thanks!

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just Enter

E2 =INDEX(B2:B5,MATCH(1,IF(D2 > A2:A5,D2 < A2:A5,1),0))<a2:a5,1),0))


[TABLE="width: 294"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]salary[/TD]
[TD]percentage[/TD]
[TD][/TD]
[TD]salary[/TD]
[TD]percentage[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]632[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]730[/TD]
[TD]7,5%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]645[/TD]
[TD]0.03[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]683[/TD]
[TD]5,7%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]736[/TD]
[TD]7,5%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

</a2:a5,1),0))

 

[Aladin Akyurek]

Try...

=INDEX($B$1:$B$4,MATCH(E1,$A$1:$A$4,1)+(LOOKUP(E1,$A$1:$A$4) < E1))

where E1 = 736.

This assumes that the data of A:B is sorted in ascending order on A.

 

[Tetra201]

Try resorting your data in range A1:B4 in descending order by column A and using the following formula:

=LOOKUP(1/E1,1/A1:A4,B1:B4)