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:

A	B
632	0%
645	3%
683	5,7%
736	7,5%

<tbody>
</tbody>

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))

	A	B	C	D	E
1	salary	percentage		salary	percentage
2	632	0		730	7,5%
3	645	0.03
4	683	5,7%
5	736	7,5%

<tbody>
</tbody>

</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)