U
ut01695
Is there a way to have the VLOOKUP function round to the higher
numerical value as opposed to the lower? MM
numerical value as opposed to the lower? MM
P
Peo Sjoblom
One way
=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"<"&C1)+1),A1:A10,0))
would be the equivalent of
=VLOOKUP(C1,A1:B10,2)
only diffrenerce is that the vlookup will find the lower closest value and
the first formula will find the
larger closest value
=INDEX(B1:B10,MATCH(SMALL(A1:A10,COUNTIF(A1:A10,"<"&C1)+1),A1:A10,0))
would be the equivalent of
=VLOOKUP(C1,A1:B10,2)
only diffrenerce is that the vlookup will find the lower closest value and
the first formula will find the
larger closest value
K
Ken Wright
Your data in the first column you are looking up against will need to be ordered
in Descending order, and you will need to use INDEX and MATCH together, with -1
as the final argument in the MATCH function, eg:-
Assuming your VLOOKUP formula was as follows:-
=VLOOKUP(D1,$A$1:$B$10,2) with the value being looked up being in D1.
The data for this in Col A would be ordered in Ascending order. To get the next
highest value, you need to resort the data based on Col A in Descending order,
and the formula would now look like this:-
=INDEX($B$1:$B$10,MATCH(D1,$A$1:$A$10,-1))
Note, the $ signs aren't really necessary, but I like em.
in Descending order, and you will need to use INDEX and MATCH together, with -1
as the final argument in the MATCH function, eg:-
Assuming your VLOOKUP formula was as follows:-
=VLOOKUP(D1,$A$1:$B$10,2) with the value being looked up being in D1.
The data for this in Col A would be ordered in Ascending order. To get the next
highest value, you need to resort the data based on Col A in Descending order,
and the formula would now look like this:-
=INDEX($B$1:$B$10,MATCH(D1,$A$1:$A$10,-1))
Note, the $ signs aren't really necessary, but I like em.