How to find matched value by searching upwards in Excel?
Normally, we are finding matched values from up to down in an Excel column. As below screenshot shown, how about finding matched value by searching upwards in Excel? This article will show you methods to 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.
You can apply the following formula to find matched value by searching upwards in Excel. Please do as follows.
1. Select a blank cell for locating the matched value, then enter formula =LOOKUP(2,1/(A2:A5=D2),B2:B5) into the Formula Bar, and then press the Enter key. See screenshot:
Note: In the formula, D2 is the cell contains the value you will find matched value based on; A2:A5 is the column range which the certain look up value locates inside. And B2:B4 contains the matched value you will get.
Then you will get the matched value by searching upwards in Excel as above screenshot shown.
This section will introduce the Look for a value in list function of Kutools for Excel to help you easily find the first matched value by searching up to down without remembering formula in Excel.
1. Select a blank cell for populating the result, then click Kutools > Formula Helper > Look for a value in list. See screenshot:
2. In the Formula Helper dialog box, you need to:
2.1 Select the table which contains the look up value and matched value in the Table_array box;
2.2 Select the cell contains the certain value you will find matched value based on in the Look_value box;
2.3 Select the column with the matched value you will find in the Column box;
2.4 Click the OK button. See screenshot:
Then you can see the first matched value based on the given value is populated in selected cell immediately.
If you want to have a free trial of this utility, please go to free download the software first, and then go to apply the operation according above steps.
- How to check if a cell value match to a list in Excel?
- How to count cells match to either X or Y in Excel?
- How to match dates by month and year only in Excel?
- How to sort a column to match the value in another column in Excel?