Have a value list and need to insert points by looking up the data table. Data table has value for points in 1000 increments. Actual value that I lookup will not be rounded to 1000 so I tried using Index and match, but I need to sort the data for less than criteria or greater than criteria. I do not want to sort the data ascending or descending. Is there anyway to achieve this.
Add a comment
|
1 Answer
You can change the lookup value instead of using greater/less than:
=INDEX(E$4:E$12, MATCH(ROUND(G4,-3),D$4:D$12,0))
The ROUND will convert 16,368 to 16,000 which will be used for the exact match, and in case of 11,549, it will become 12,000 for another exact match.
-
This worked like charm Jerry...Great help!! Please help for similar situation with values increment of 5s. 10, 15, 20, 25. I need to round the value. E.g. up to 12.5 to 10 OR greater 12.5 to 15. Commented Apr 12, 2014 at 13:57
-
@roman_bull For increments of 5, you can use
MROUND():=INDEX(E$4:E$12, MATCH(MROUND(G4,5),D$4:D$12,0))– JerryCommented Apr 12, 2014 at 14:01 -
-