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.LOOKUP EXCEL


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. – roman_bull Apr 12 '14 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)) – Jerry Apr 12 '14 at 14:01
  • You made my day...!!! – roman_bull Apr 12 '14 at 14:06
  • @roman_bull You're welcome :) – Jerry Apr 12 '14 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.