I have a table where one column has numbers from 1 to 25 and another has values corresponding to those numbers.

What I am trying to do is make it so when I change the number in one cell another cell changes to the corresponding number based on the table.

Do I have to do this manually or is there a way to set a range for the cases and values?

Edit: The Sheet.

My awful attempts at this just to explain what I am trying to do are in cells B8 and B9

  • 1
    You have not provided enough information to help you solve this issue. Remember that, while you can see your data, ranges, layout, sheet names, etc., we cannot. So we have no points of reference to actually suggest anything. The most effective and efficient way to receive help is to share a link to your sheet (or a copy of it or a realistic representative sample from it), being sure to set the links' Share permission to "Anyone with the link..." and "Editor." At a guess, I don't think you need SWITCH; I think you need VLOOKUP.
    – Erik Tyler
    Jan 21, 2021 at 14:54
  • @ErikTyler Thanks for the suggestion! I added it in an edit.
    – Yuval Amir
    Jan 21, 2021 at 15:00
  • 1
    I've looked at your link... and there is still not enough information. Your post says, "...when I change the number in one cell another cell changes to the corresponding number based on the table." Where is the "one cell"? Where is "another cell"? What part "on the table" should be returned? Please be as SPECIFIC as you can (e.g., "I want to type a number in Calculator!B1 and have that looked up in the 'Base Damage Per Level' sheet column 2 and returned to Calculator!B2. The answer I expect to see in Calculator!B2 right now if I type 20 into Calculator!B1 would be _________.")
    – Erik Tyler
    Jan 21, 2021 at 15:58
  • @ErikTyler As you pointed out at first, I was using the wrong function, however just for the sake of it, I wanted B8 and B9 to change when I change B1, according to the "Base Damage Per Level" sheet.
    – Yuval Amir
    Jan 22, 2021 at 9:29

1 Answer 1


Apparently, I was just trying to use the wrong function, like @ErikTyler pointed out, and I needed to use the LOOKUP function.

  • You may also want to look into VLOOKUP, Yuval, since it is more straightforward and has fewer restrictions. For instance the VLOOKUP version of your B8 formula would be as follows: =VLOOKUP(B1,'Base Damage Per Level'!A:B,2,FALSE)
    – Erik Tyler
    Jan 22, 2021 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.