( 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:
=if(P2<Reference!N4,Reference!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.
Solution:
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.
I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html
This comment has been removed by the author.
ReplyDeleteI am highly appreciative. This worked almost flawlessly. Only thing I changed is made it '<=' so it adjusted /on/ the value. I apologize if I didn't make that clear enough. Very impressive work, thank you.
ReplyDeleteAfter some time of examining it, I think I'm starting to understand what the formula does. I'm having an issue, though. I duplicated it and am trying to get it to work on another table. Here's what I have:
ReplyDelete=query(Reference!A3:J;"select E where A="&C3&" order by A desc limit 1")
The A column and C3 are strings. The formula seems to be trying to interpret C3 into a column, looking for said column instead of searching for it in the table.
After much experimentation, I discovered I had to add single quotes outside of the double quotes.
DeleteYes, you need to add single quotes for text values... and for numeric values you don't need to add single quotes..
Delete