When i am doing a lookup how can use the closest match not the next higher or lower.

 

For example 

 

I have two fields:

 

1) Version

2) Service Pack

 

I run a lookup that takes the version number from one table and gets the match from the next table and copies the Service Pack.

 

In the case that there is no match i am forced to use either the next higher value or next lower value.

 

I want to choose the closest value be it higher or lower. How can i do that?

 

Thanks again!

 

-Erik

I want to choose the closest value be it higher or lower. How can i do that?

 

Use a calculation field, not a lookup. Get both the next higher and next lower values, using the LookupNext() function, and select the one that's closer to the original value.

 

IIUC, you want to get the closest version number first - then use that to lookup (or just display) the Service Pack, using a second relationship based on the calculation field.

 

Edited June 20, 201411 yr by comment