New Contributor

I need to pull out a value from a table. If A=250, in another table the values are say..250, 280, 350, 400. and the second column has another value . Like for 250 is 30, 280 is 35, 350 is 40.... so on. If I have A=260 then how do I pull out the value. Which means I need 35 which is A>250 and A< 280. so the corresponding value of 280 is 35. 


8 Replies
Two questions.
1. What value do you want for an exact match? So, when A=250 or A=280
2. If the target is not an exact match, you want the value from the next *higher* number. So, for 281 you want 40. Correct?


Yes the target is not an exact match and so we want to take the next higher number.


If you have Office 365, you can use Xlookup, and use the option for "Exact match and next higher value".
best response confirmed by allyreckerman (Microsoft)


That could be like

LOOKUP(value, {0, 250, 280, 350, 400},{1,2,3,4,5})

if use another table data instead

Thank you very much


You are welcome. But better not to hardcode constants inside the formula but use table with them. Above is only idea.

Yes I did that. I did not hardcore it but used the values from a table.