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 find the highest value and return adjacent cell value in Excel?

doc-get-largest-related-value-1 1

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.

Find the highest value and return adjacent cell value with formulas

Find and select the highest value and return adjacent cell value with Kutools for Excel


Find and select the largest value in a column and return corresponding data:

With Kutools for Excel’s Select Cells with Max & Min Value utiltiy, you can quickly select the largest or smallest number in a column or a range as you need.

doc-get-largest-related-value-7 7


arrow blue right bubble Find the highest value and return adjacent cell value with 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:

doc-get-largest-related-value-2 2

Notes:

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:

doc-get-largest-related-value-3 3


arrow blue right bubble Find and select the highest value and return adjacent cell value with Kutools for Excel

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:( Download Kutools for Excel Now! )

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 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:

doc-get-largest-related-value-4 4

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:

doc-get-largest-related-value-5 5  2 doc-get-largest-related-value-6 6

Click to Download Kutools for Excel and free trial Now!


Related article:

How to find highest value in a row and return column header in Excel?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
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.
    Maurice · 1 years ago
    I GET #NA what am I doing wrong? Thanks
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    Thank you very much for sharing, I was having real trouble with this function. Of course in hindsight, it's as clear as glass!

    Thanks Again & Best Regards,

    Jon
  • To post as a guest, your comment is unpublished.
    Mary · 1 years ago
    Hello,

    I need your help. For this table:

    A B C
    2002 1 4 7
    2003 2 5 8
    2004 3 6 9


    I want that excel returns the year with the highest value for each column. For example, for column A, I want a formula that returns "2004". Can you, please, help me?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Viet · 1 years ago
    any way we can find the minimum of the cells selected and return the value in the cell adjacent. kutools only allow for data range selection.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Viet,

      As you said, the Kutools only can select the max or min values in selection.
      To find the minimum value and return the data in adjacent cell, you just need to apply the following formula:
      =INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)).
      Please try it, Thank you!
  • To post as a guest, your comment is unpublished.
    Damrod · 2 years ago
    Like the 2 above me already asked, how can we get multiple values if there are multiple max ones?
    For example, you have 2 items in a list of 100 items that are both 92% and the rest is below that, how does one get both items with that percentage instead of just one?
  • To post as a guest, your comment is unpublished.
    Justin · 2 years ago
    Hello,

    How do you return two values from the range if there are two max numbers? For example, both Tom and Nicol have 92, so how would you make the cell contain both Tom AND Nicol? Thank you.
  • To post as a guest, your comment is unpublished.
    beezy · 3 years ago
    What if you have duplicate values?