Forum Discussion

Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Jan 28, 2019
Solved

Vlookup - highest value

High all,   I have a table with 3 columns:   A text based column with a unique code, a product name column and a column with amounts. The thing is that I have to search into this table on produc...
excel
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Jan 28, 2019

    Hi Dennis,

     

    Please try this formula:

    =INDEX($C$1:$C$24,MATCH(MAX(IF(F6=$B$1:$B$24,SUBSTITUTE($A$1:$A$24,"-","")+0)),SUBSTITUTE($A$1:$A$24,"-","")+0,0))

     

    Please note that you have to press https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7 each time you enter this formula, and after you open it in the edit mode.

     

    Hope that helps

    Regards

Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Jan 28, 2019

Hi Dennis,

 

Please try this formula:

=INDEX($C$1:$C$24,MATCH(MAX(IF(F6=$B$1:$B$24,SUBSTITUTE($A$1:$A$24,"-","")+0)),SUBSTITUTE($A$1:$A$24,"-","")+0,0))

 

Please note that you have to press https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7 each time you enter this formula, and after you open it in the edit mode.

 

Hope that helps

Regards

Dennis Depoorter's avatar
Dennis Depoorter
Brass Contributor
Jan 28, 2019
Thanks Haytham,... this works like a charm!!!
  • datphamict's avatar
    datphamict
    Copper Contributor
    Feb 03, 2021

    Dennis Depoorter i can hlep you https://tinhocvanphong.net/huong-dan-su-dung-ham-vlookup-trong-excel/

    • MarcoNardi's avatar
      MarcoNardi
      Copper Contributor
      Apr 30, 2021

      Hi, I'm trying to find the highest value in each column and return the corresponding cell values from the header and the last column. Really would appreciate some direction. Best, M

       

      running | jumping | cycling | walking | InjuryType

      0           |  3            |       2    |   0         |     Broken bones

      20         |    1          |      5     |    12      |     sprains

      20         |  0            |       1    |   5         |     falls

      12         |  40          |     20    |   40       |     dislocations

       

      Output:

      Running 20 Sprains

      Running 20 Falls

      jumping 40 Dislocations

      cycling 20 Displocations

      walking 40 dislocations

       

       

       datphamict 

Resources