+ Reply to Thread
Results 1 to 6 of 6

Complex IF function

  1. #1
    Registered User
    Join Date
    07-28-2016
    Location
    Chatham
    MS-Off Ver
    2016
    Posts
    93

    Question Complex IF function

    Hi there,


    I have an issue with choosing the right way of performing an action:

    I have a table for scoring the procurement price/quality evaluation results (attached). I need to be able to place MAX (i.e. the best total result) of the 18th row from the 2nd sheet (Scores) into D9 of the 3rd sheet (Feedback). However, based on this maximum, I need to fill all the above D3:D8 cells of the 3rd sheet (Feedback) with the results of that Winner (who got maximum) that are stored in rows 4-7 and 16-17 of the 2nd sheet (Scores), which I would like to ask for help with.

    The problem is that I never know which of the suppliers will win and I need excel to be able to understand and fill all the relevant information without any of my assistance (although it only take 20 seconds, I still would like to eliminate that).

    If there is a need for any thorough clarification, I am very happy to do it.

    Hope anyone can help, I would be extremely grateful.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Complex IF function

    Could you please fill in and highlight some expected results on the relevant sheet of your workbook?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-28-2016
    Location
    Chatham
    MS-Off Ver
    2016
    Posts
    93

    Re: Complex IF function

    Hey, thanks for your interest to help!

    I have highlighted in purple the main aspects needed for this particular example.
    However, for instance, if the winner will not be in G column in Scores sheet, but will be in E column, I would need all the scoring in the Feedback sheet to change onto the ones that are in the E column. And the trigger for that change should be the MAX result in the 18th row of the Scores sheet - whoever is the largest - all of his info needs to go to D3:D8 (only those) of the Feedback sheet.

    Hope it makes sense now.

    Should you need nay further clarification, please ask!
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Complex IF function

    in D3

    =INDEX(Scores!$D$4:$G$7,ROWS($1:1),MATCH(Feedback!$D$9,Scores!$D$18:$G$18,0))

    Copy down to row 6

    in D7

    =INDEX(Scores!$D$16:$G$16,,MATCH(Feedback!$D$9,Scores!$D$18:$G$18,0))

    inn D8

    =INDEX(Scores!$D$17:$G$17,,MATCH(Feedback!$D$9,Scores!$D$18:$G$18,0))

  5. #5
    Registered User
    Join Date
    07-28-2016
    Location
    Chatham
    MS-Off Ver
    2016
    Posts
    93

    Re: Complex IF function

    Dear John, this is exactly what I needed! Thank you very much indeed, it is awesome that such professionals like you and Ali are here to help!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Complex IF function

    Thank you for the feedback.

    Could you please mark thread as solved ("Thread Tools" at top of first post).

+ 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. Need help on complex IF function
    By quixoti in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2014, 01:50 PM
  2. Complex if function help
    By Adamlearnexcel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-17-2012, 12:05 PM
  3. Complex Function
    By wtb0001 in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 06-01-2011, 08:28 PM
  4. Creating a function to simplify a more complex function
    By mk74 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2011, 08:43 AM
  5. Complex IF function
    By kents86 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2010, 07:50 AM
  6. Complex function
    By Arby in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-30-2007, 01:14 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