Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to return a value if a given value exists in a certain range in Excel?

As the left screenshot shown, if a given number 5 is in a certain number range, how to return the value in the adjacent cell. The formula method in this article can help you achieve it.

Return a value if a given value exists in a certain range by using a formula


Easily select entire rows based on cell value in a certian column:

The Select Specific Cells utility of Kutools for Excel can help you quickly select entire rows based on cell value in a certian column in Excel as below screenshot shown. After selecting all rows based on cell value, you can manually move or copy them to a new location as you need in Excel.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Return a value if a given value exists in a certain range by using a formula


Please apply the following formula to return a value if a given value exists in a certain range in Excel.

1. Select a blank cell, enter formula =VLOOKUP(E2,A2:C8,3, TRUE) into the Formula Bar and then press the Enter key. See screenshot:

You can see the given number 5 is in the number range 4-6, then the corresponding value Addin 012 in the adjacent cell is populated into the selected cell immediately as above screenshot showed.

Note: In the formula, E2 is the cell contains the given number, A2:C8 contains the number range and the value you will return based on the given number, and number 3 means that the value you will return locates in the third column of range A2:C8. Please change them based on your need.


Related Articles:



Recommended Productivity Tools for Excel

Kutools for Excel Helps You Always Finish Work Ahead of Time, and Stand Out From Crowd

  • More than 300 powerful advanced features, designed for 1500 work scenarios, increasing productivity by 70%, give you more time to take care of family and enjoy life.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

Brings Tabbed Browsing and Editing to Microsoft Office, Far More Powerful Than The Browser's Tabs

  • Office Tab is designed for Word, Excel, PowerPoint and Other Office Applications: Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Teresa · 9 months ago
    Doesn't work. if only it was that simple, wouldn't need to be googling for this
    • To post as a guest, your comment is unpublished.
      Eira Noire · 12 days ago
      It obviously does. Instead of whining and trying to be a smartass about it, you could have at least tried it out.
    • To post as a guest, your comment is unpublished.
      Mika Donner · 12 days ago
      It does. Try it out before rudely complaining, simple as that.