How to find the highest value and return adjacent cell value in Excel?
If you have a range of data as following screenshot shown, now, you want to find the largest value in column A and get its adjacent cell content in column B. In Excel, you can deal with this problem with some formulas.
Take the above data for example, to get the largest value of its corresponding data, you can use the following formulas:
Please type this formula: =VLOOKUP(MAX($A$2:$A$11), $A$2:$B$11, 2, FALSE) into a blank cell you need, and then press Enter key to return the correct result, see screenshot:
1. In the above formula, A2:A11 is the data range that you want to know the largest value, and A2:B11 indicates the data range you used, the number 2 is the column number that your matched value is returned.
2. If there are multiple largest values in column A, this formula is only get the first corresponding value.
3. With the above value, you just can return the cell value from the right column, if you need to return the value which in the left column, you should apply this formula: =INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0))( A2:A11 is the data range that you want to get the relative value, B2:B11 is the data range which contains the largest value ), and then press Enter key. You will get the following result:
The above formulas only can help you to return the first corresponding data, if there are duplicate largest number, it will not help. Kutools for Excel's Select Cells with Max & Min Value utility may help you to select all the largest number, and then you can easily view the corresponding data which adjancent of the largest number.
After installing Kutools for Excel, please do as follows:
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
1. Select the number column that you want to find and selecte the largest values.
2. Then click Kutools > Select > Select Cells with Max & Min Value, see screenshot:
3. In the Select Cell With Max & Min Value dialog box, choose Maximum value from the Go To section, and select Cell option in the Base section, then choose All cells or First cell only that you want to select the largest value, click OK, the largest number in column A has been selected and then you can get the corresponding data which adjancent them, see screenshots:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, 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!