I need to look up a value from a list based on a calculation

English
#1

C:\fakepath\Screenshot (1).png

So in this screenshot, Cells B5 to B21 need to compare the data in cells C5 to C21 to the data over in Column I, to get the value listed in Column J.

So, for example, B5 should look at C5, and if C5 was “22”, it would display “Lieutenant, JG”
B15 would look at C15 and if the result there was “145”, It would display “Rear Admiral **”

Each of the B-cells is a separate lookup.

Cell B3 is the same, but uses column K.

I know there is a simple way to do it, but it has been ages since I worked spreadsheets, and the solution used back then was a massive nested If-Then for each cell.

Here is the original file…
SFB Rank Sheet.ods

#2

Hello, please upload your sample file shown in the screenshot so a possible solution could be tested before an answer is posted. This helps to avoids endless comment threads due to simple typos.


But one thing in advance: Clearly the task will be much easier if column `I` could be split into 2 columns which define `min` and `max` (or `from` and `to`) values as numbers, since comparing numbers (columns `C` through `F`, with textual number ranges in columns `I` and `K` requires text manipulation and conversion to numbers.
#3

Hello

Preface

  1. I don’t understand your formula in column C, which adds a Loss (column D) to the score. I kept it as it was in the original Sheet, but I would expect a Loss to not count to the Score or being even subtracted from the sum of Win + Draw
  2. I have divided - as per my comment above column I into 2 columns labeled from and to to enable a simple numerical comparision of the Score to the range beginnings (column I labeled from).

Please see the attached sample file with sample, randomized data (grey background):

SFBRankSheet.ods

The idea of the formula (e.g. =OFFSET($K$1;COUNTIF(I$3:I$15;"<=" & C10)+1;0;1;1)):

  • Count how many values in range I$3:I$15 are lower than than a given score
  • get the value in column K using starting point in row 1 and move down the proper value given by the count from previous step.

Hope that helps.

#4

I realized as I went to bed that I needed to tweak the sums. However the goal will be to track plays as well as looses, and adding is easier for players to understand. A lost is going to net them 1 point (so that even a loss counts as a play), draws 2 points, and wins 3. That tweak I can do.

I just didn’t want to sit there and nest a tone of if/thens like the last time I built a sheet like this some five or six years ago (maybe longer). I am no expert, and only once in a while do I decide a spreadsheet will do what I need.I will take a look at what you did, and see if I can wrap my aging brain around it. =)

#5

Good news! I think I understand it. It took me a bit of reading, and looking up documentation results, but I think I got it, and should be able to finish the sheet.Thank you, this is a lot easier than a bunch of If/Thens.

#6

If the answers works for you, please consider to click the check mark (:heavy_check_mark:) next to the answer. Thanks in advance …

#7

Ok, Finally got a chance to Apply the modifications, including tweaking the totals you get for wins, losses, and draws. However, while it works, it fails as soon as the top of the chart is exceeded. So we need a tweak that will tell it something like:

“If the total score is higher than X, use the highest listed result”

If we get that figured out, we’ll have it.

#8

I changed the file to the latest version.

#9

I’m closing this off. I figured out how to use a single IF statement to look and see if the offset/countif even needed to be done. Thank you!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.