+ Reply to Thread
Results 1 to 16 of 16

Help Looking Up a Value With Multiple Criteria & Chacking if Criteria Are Within Bounds

  1. #1
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Help Looking Up a Value With Multiple Criteria & Chacking if Criteria Are Within Bounds

    Help I need formula help please

    I'm looking to populate the date in my active sheet named "Project Tracker". cell AM8 based on the drop down from cell AH8 (image 1). The data with different timelines is on a 2nd sheet called "T-Structure" (image 2). I want the sheet Project Tracker, cell AM8 (image 1) to look up based on the dropdown in Project Tracker, cell AH8 and the category on project tracker, cell AM3 the value of the cell that matches on the T-Structure which would be cell D4 on T-Structure (image 2). The cell D4 then needs subtract from the Project Tracker, Estimated Completion Date, which would be cell AJ8 to return the FINAL date in cell AM8.

    1.png

    2.png

    It's complex because the Project Tracker sheet has many categories, which I have formulas to let me know if a particular category is in scope based on a drop down on Project Tracker, cell C8. Base on the drop downs in C8 the sheet enters across N/A into categories that aren't in scope. Below is the existing code already in the cell. I'm looking for the existing code to work with the above request.

    Here is the current code in AM8. Again, only looking to add in the above request into this code somehow to make it all work. Lookup the corresponding number from the matrix on T-Structure to provide date on Project Tracker but also keep the code to make the sheet dynamic in AM8.

    Current Code in AM8

    =IF($C8="","",IF($C8="Kickoff, Panel Replacement, Door Alarm Remediation, Integration, Training & Go Live",$AJ8-$AM$5,IF($C8="Kickoff, Panel Replacement, Integration, Training & Go Live",$AJ8-$AM$5,IF($C8="Kickoff, Door Alarm Remediation, Integration, Training & Go Live",$AJ8-$AM$5,"N/A"))))

    Note - Project Tracker Table Range is A3:FL146

    T-Structure Table Range is A3:EC12

    Dropdowns from Project Tracker, cell AH8 are on T-Structure Sheet, A4:A12




    Thank you to anyone who can figure this out for me.

    Regards,
    Kevin
    Attached Images Attached Images
    Last edited by kcstier; 12-16-2021 at 12:11 PM. Reason: Moderator says to update title

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Tricky trying to write this from just pictures but I think in AM8:

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    HTH
    Regards, Jeff

  4. #4
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Thanks this helped me out. How about the 2nd part of the existing formula that was already in the cell? The,

    =IF($C8="","",IF($C8="Kickoff, Panel Replacement, Door Alarm Remediation, Integration, Training & Go Live",$AJ8-$AM$5,IF($C8="Kickoff, Panel Replacement, Integration, Training & Go Live",$AJ8-$AM$5,IF($C8="Kickoff, Door Alarm Remediation, Integration, Training & Go Live",$AJ8-$AM$5,"N/A"))))

    How would I incorporate this into your provided formula above? (=$AJ8-INDEX('T-Structure'!$B$4:$EC$12,MATCH($AH8,'T-Structure'!$A$4:$A$12,0),MATCH(AM$3,'T-Structure'!$B3:$EC3,0))

    Thanks

  5. #5
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    How do I edit the title? Thanks

  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Try like this:

    Please Login or Register  to view this content.
    WBD

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Re-read Post 3.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  8. #8
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Quote Originally Posted by Glenn Kennedy View Post
    Re-read Post 3.
    How do I edit the current thread title?

  9. #9
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Quote Originally Posted by jeffreybrown View Post
    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    How do I edit this thread title?

  10. #10
    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: Help Needed Writing a formula Formula Help Excel Please Assist

    READ the instructions you have been given already!!! They say:

    To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
    Help I need formula help fellas
    We are not all 'fellas'
    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.

  11. #11
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Thanks, I've updated the title. Is the new title better? Can you please add back WideBoyDixon's post from 2:19am that was deleted?
    Last edited by AliGW; 12-16-2021 at 12:00 PM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Assistance Needed with vlookup, index and match, if statements

    Restored in all its original glory.

  13. #13
    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: Assistance Needed with vlookup, index and match, if statements

    Is the new title better?
    Only marginally. You haven't stated in the title what you are trying to achieve. I would suggest something along these lines: "Looking Up a Value With Multiple Criteria & Chacking if Criteria Are Within Bounds".

    Your title should never be simply the name or names of functions.

    Glenn has allowed the thread to restart, however I would appreciate your updating the title again with my suggestion. Thank you.

  14. #14
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Assistance Needed with vlookup, index and match, if statements

    Updated the title. Thanks.
    Last edited by AliGW; 12-16-2021 at 12:14 PM. Reason: PLEASE don't quote unnecessarily!

  15. #15
    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: Help Looking Up a Value With Multiple Criteria & Chacking if Criteria Are Within Bound

    Thank you.

  16. #16
    Registered User
    Join Date
    07-18-2016
    Location
    Illinois
    MS-Off Ver
    2010
    Posts
    53

    Re: Help Needed Writing a formula Formula Help Excel Please Assist

    Quote Originally Posted by WideBoyDixon View Post
    Try like this:

    Please Login or Register  to view this content.
    WBD
    WideBoyDixon, thank you for your help. I forgot to update. Your above code worked =) Thank you again and hope you had a nice holiday.

+ 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. Replies: 3
    Last Post: 12-12-2019, 04:08 AM
  2. [SOLVED] Excel formula assist in looking for sequence
    By brian8989 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-08-2019, 02:42 AM
  3. Help Needed with Writing Top 3 and Bottom 3 Formula
    By Moonhana in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2017, 06:47 PM
  4. [SOLVED] Help needed with writing an If/Then conditional formula regarding formatting
    By judybthe1 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-08-2015, 02:45 PM
  5. Help needed for creating array and writing formula in VBA, code included
    By yanheng in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-14-2012, 02:00 PM
  6. Help needed Writing formula that totals data at end of column
    By sid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-12-2006, 02:25 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