3 Replies Latest reply on May 12, 2018 1:03 PM by Okechukwu Ossai

# LOOKUP LOOP??? HELP ME!!!

[My_calc_field]

IF [value1] < LOOKUP([value2], -2) THEN

LOOKUP([value2], -2)

ELSEIF [value1] < LOOKUP([value2], -3) THEN

LOOKUP([value2], -3)

ELSEIF [value1] < LOOKUP([value2], -4) THEN

LOOKUP([value2], -4)

ELSEIF [value1] < LOOKUP([value2], -5) THEN

LOOKUP([value2], -5)

ELSEIF [value1] < LOOKUP([value2], -6) THEN

LOOKUP([value2], -6)

ELSEIF [value1] < LOOKUP([value2], -7) THEN

LOOKUP([value2], -7)

...

...

...

...

...

...

ELSEIF [value1] < LOOKUP([value2], -99) THEN

LOOKUP([value2], -99)

ELSEIF [value1] < LOOKUP([value2], -100) THEN

LOOKUP([value2], -100)

END

• ###### 1. Re: LOOKUP LOOP??? HELP ME!!!

Can you attach a sample workbook?

It is difficult to suggest a solution without knowing the context the formula is being applied. What is [value1]? Is it a table calculation or just a pre-aggregated field? Is [value1] constant for every row?

There might be simpler solutions if you can post a sample packaged workbook with fake data.

Ossai

• ###### 2. Re: LOOKUP LOOP??? HELP ME!!!

value1 | value2 | My_calc_field |      <=====    I want calc filed

---------+---------+-----------------+

25   |    12    |                       |

---------+---------+-----------------+

18   |    19    |                       |

---------+---------+-----------------+

12   |    22    |                       |

---------+---------+-----------------+

27   |    88    |                       |

---------+---------+-----------------+

41   |    17    |                       |

---------+---------+-----------------+

44   |    55    |          88         |

---------+---------+-----------------+

32   |    14    |                       |

---------+---------+-----------------+

33   |    66    |          55         |

---------+---------+-----------------+

57   |    57    |          88         |

---------+---------+-----------------+

21   |    42    |          66         |

---------+---------+-----------------+

11   |    38    |          57         |

---------+---------+-----------------+

19   |    27    |          42         |

---------+---------+-----------------+

48   |    29    |          57         |

---------+---------+-----------------+

16   |    17    |          27         |

---------+---------+-----------------+

99   |    12    |                       |

---------+---------+-----------------+

81   |    59    |          88         |

---------+---------+-----------------+

77   |    46    |          88         |

---------+---------+-----------------+

24   |    24    |          59         |

---------+---------+-----------------+

25   |    11    |          46         |

---------+---------+-----------------+

25   |    54    |          46         |

---------+---------+-----------------+

....

....

....

....

....

....

....

[value1] < lookup([value2], -2) then lookup([value2], -2)

[value1] < lookup([value2], -3) then lookup([value2], -3)

[value1] < lookup([value2], -4) then lookup([value2], -4)

[value1] < lookup([value2], -5) then lookup([value2], -5)

[value1] < lookup([value2], -6) then lookup([value2], -6)

......

.....

....

....

[value1] < lookup([value2], -100) then lookup([value2], -100)

I want recent data(lookup([value2], -x) of [value1] < lookup([value2], -x)).

I can't speak english.. Sorry!!

Plz... Help Me!!!

• ###### 3. Re: LOOKUP LOOP??? HELP ME!!!

I am not able to provide a solution. Not enough information.