+ Reply to Thread
Results 1 to 4 of 4

Max and reference cell. Use Vlookup or Index/Match?

  1. #1
    Registered User
    Join Date
    08-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    2

    Max and reference cell. Use Vlookup or Index/Match?

    Hi All,

    This is my issue:

    I have a range of value that is in a single row.
    I need to calculate the maximum of every other cell and return the value/text on the cell aside.

    Example
    1 text 1 2 text 2 3 text 3 4 text 4 5 Text 5

    In this specific example I need to calculated the max of cells 1,3,5,7,9 and return the value/text of the cell on the right (2 or 4 or 6 or 8 or 10) for the one that is maximum.
    In this instance the result would be "text 5"

    I tried with VLOOKUP and the MAX but I can only have the specific column in the array, not the column at the very right.

    Anybody has idea?

    Thanks,

    Andrea

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Max and reference cell. Use Vlookup or Index/Match?

    Welcome to Excel Forum.
    Assuming that the row in question is row one, thy this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let me know if you have any questions.

    Edit: the formula originally in this post would not have worked.
    Last edited by JeteMc; 08-11-2016 at 12:10 AM. Reason: Correcting an error in formula
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-10-2016
    Location
    New York
    MS-Off Ver
    2010
    Posts
    2

    Re: Max and reference cell. Use Vlookup or Index/Match?

    Thanks JeteMc!!! That works

    I actually had to change the formula to:

    =INDEX(A1:J1,,MATCH(MAX(A1,C1,E1,G1,J1),A1:J1,0)+1)

    Since some of the values on the "possible results" cells are number as well.

    But it works perfectly.

    Thanks again

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Max and reference cell. Use Vlookup or Index/Match?

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index Match / VLookup to reference Worksheets
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2016, 09:29 AM
  2. Replies: 4
    Last Post: 01-13-2014, 04:16 AM
  3. Problems with reference to multiple arrays using VLOOKUP / INDEX-MATCH
    By tnuis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2013, 07:21 AM
  4. #Value error when using INDEX and MATCH to reference a cell
    By willow77 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-01-2013, 08:56 AM
  5. Using INDEX MATCH to reference a cell that has a CONCATENATE
    By myshadeofglory in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2011, 01:40 PM
  6. Replies: 7
    Last Post: 08-10-2010, 04:05 PM
  7. [SOLVED] INDEX - MATCH - VLOOKUP - returning missing reference
    By njuneardave in forum Excel General
    Replies: 2
    Last Post: 06-27-2006, 02:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1