 SOLVED

excell

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

Re: excell

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?

Re: excell

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

Re: excell

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)
Solution

Re: excell

That could be like

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

if use another table data instead

Re: excell

Thank you very much

Re: excell

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

Re: excell

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

Great!