( by Alzarath )
I'm far from an experienced spreadsheet user. Most of what I have done has been from searching and adapting.
I want my formula to (for example) scan the B column and, if one of the fields match a given field, it'll acquire the same row that it matches, but for C.
Here's an example, assuming A2 is the formula and A1 is the input:
B1 is 5, B2 is 17, B3 is 15, etc.
C1 is 0, C2 is 1000, C3 is 2500 etc.
If A1 is 500, A2 will be 5 (B1). If A1 is 1100, A2 will be 17 (B2), and so on.
What I currently have (keep in mind this doesn't follow the example) is this:
Basically a big if-then-else-then-else-etc statement. I'd very much like a way to crop this down significantly, if it's possible, and make it so I only have to adjust a value or two in case the table were expanded, instead of adding all of the values manually.
Have a look at the following screenshot of Sheet "Reference":
And following is the screenshot of Sheet2:
And I have inserted the following formula in Cell M3:
=query('Reference'!M3:N;"select M where N<"&P2&"order by N desc limit 1")
I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.