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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
- How to vlookup value and return true or false / yes or no in Excel?
- How to vlookup return value in adjacent or next cell in Excel?
- How to return value in another cell if a cell contains certain text in Excel?
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoawesome, thanks!
To post as a guest, your comment is unpublished.· 1 years agoDoesn'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.· 6 months agoIt 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.· 6 months agoIt does. Try it out before rudely complaining, simple as that.