SOLVED

excell

%3CLINGO-SUB%20id%3D%22lingo-sub-2397753%22%20slang%3D%22en-US%22%3Eexcell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397753%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20pull%20out%20a%20value%20from%20a%20table.%20If%20A%3D250%2C%20in%20another%20table%20the%20values%20are%20say..250%2C%20280%2C%20350%2C%20400.%20and%20the%20second%20column%20has%20another%20value%20.%20Like%20for%20250%20is%2030%2C%20280%20is%2035%2C%20350%20is%2040....%20so%20on.%20If%20I%20have%20A%3D260%20then%20how%20do%20I%20pull%20out%20the%20value.%20Which%20means%20I%20need%2035%20which%20is%20A%26gt%3B250%20and%20A%26lt%3B%20280.%20so%20the%20corresponding%20value%20of%20280%20is%2035.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2397753%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397801%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397801%22%20slang%3D%22en-US%22%3ETwo%20questions.%3CBR%20%2F%3E1.%20What%20value%20do%20you%20want%20for%20an%20exact%20match%3F%20So%2C%20when%20A%3D250%20or%20A%3D280%3CBR%20%2F%3E2.%20If%20the%20target%20is%20not%20an%20exact%20match%2C%20you%20want%20the%20value%20from%20the%20next%20*higher*%20number.%20So%2C%20for%20281%20you%20want%2040.%20Correct%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397819%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891695%22%20target%3D%22_blank%22%3E%40tusharm10%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20the%20target%20is%20not%20an%20exact%20match%20and%20so%20we%20want%20to%20take%20the%20next%20higher%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2397940%22%20slang%3D%22en-US%22%3ERe%3A%20excell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2397940%22%20slang%3D%22en-US%22%3EIf%20you%20have%20Office%20365%2C%20you%20can%20use%20Xlookup%2C%20and%20use%20the%20option%20for%20%22Exact%20match%20and%20next%20higher%20value%22.%3C%2FLINGO-BODY%3E
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?

@tusharm10 

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

@udhoot7141 

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

@udhoot7141 

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.

@udhoot7141 

Great!