+ Reply to Thread
Results 1 to 13 of 13

INDEX-MATCH multiple criteria - with a variable value in a cell

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    Oakland
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    INDEX-MATCH multiple criteria - with a variable value in a cell

    God bless whoever can help me with this. Bear with me, things have been altered to protect some information so hopefully this makes sense.

    My goal: create a index and match function in X4 that will retrieve a value from 'RLB Index'.

    The issue: The data on 'RLB Index' is sorted into three locations: 1,2 and 11. The lookup value for the location is D3, and the validated options are locations 1-10. What I want to do, is force the index/ match function to use location 11 if locations 8-10 are selected in D3.

    This is the equation I have so far that just uses D3 as-is.


    {=INDEX('RLB Index'!$C$2:$E$53,MATCH('UC Worksheet'!F4&G4,'RLB Index'!$A$2:$A$53&'RLB Index'!$B$2:$B$53,0),MATCH('UC Worksheet'!$D$3,'RLB Index'!$C$1:$E$1,0))}

    Huge thanks to anyone that can help.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 10-23-2019 at 02:07 PM.

  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,340

    Re: force the index/ match function to use location 11 if locations 8-10 are selected in D

    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.

    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. As you are new here, I have done it for you this time)
    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
    10-23-2019
    Location
    Oakland
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: force the index/ match function to use location 11 if locations 8-10 are selected in D

    Thank you Ali! I'll make sure to fix that going forward.

  4. #4
    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,891

    Re: force the index/ match function to use location 11 if locations 8-10 are selected in D

    I can't tell if the title I see is modified or not... so I'll give you the benefit of the doubt and change it (again??) to something that looks like a term you'd use in a Google search..

    Formula:
    =INDEX('RLB Index'!$C$2:$E$53,MATCH(1,INDEX((F4='RLB Index'!$A$2:$A$530)*(G4='RLB Index'!$B$2:$B$53),0),0),MATCH(IF(D3>2,11,D3),'RLB Index'!$C$1:$E$1,0))

    normal enter is required (non-array)
    Attached Files Attached Files
    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

  5. #5
    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,891

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    I think you maybe did change it... but not enough. It should ideally look like the words you'd use in a Google search for your solution.

    You can expect that further posts with weak titles will be locked until YOU make the required changes.

  6. #6
    Registered User
    Join Date
    10-23-2019
    Location
    Oakland
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    Glenn - thank you very much! Is it possible to achieve those results if the locations were names of cities rather than numbers?

  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,891

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    You may need to $ some cell refs (in red) if copying tyour revised formula down:

    =INDEX('RLB Index'!$C$2:$E$53,MATCH(1,INDEX((F4='RLB Index'!$A$2:$A$530)*(G4='RLB Index'!$B$2:$B$53),0),0),MATCH(IF(D$3>2,11,D$3),'RLB Index'!$C$1:$E$1,0))

  8. #8
    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,891

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    Post a sheet with REALISTIC data. Don't use numbers if text is what is present...

  9. #9
    Registered User
    Join Date
    10-23-2019
    Location
    Oakland
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    Glean - apologies for not posting realistic data. I have edited the original file and reloaded (worksheet #2). I have edited the original post to indicate text rather than numbers. Thank you for your help.

    My goal: create a index and match function in X4 that will retrieve a value from 'RLB Index'.

    The issue: The data on 'RLB Index' is sorted into three locations: Location 1, Location 2 and Locatoin 11. The lookup value for the location is D3, and the validated options are locations 1-10.
    What I want to do, is force the index/ match function to use location 11 if locations 8-10 are selected in D3.
    Attached Files Attached Files

  10. #10
    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,891

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    Use:

    =INDEX('RLB Index'!$C$2:$E$53,MATCH(1,INDEX((F4='RLB Index'!$A$2:$A$530)*(G4='RLB Index'!$B$2:$B$53),0),0),MATCH(IF($D$3="Location 1","Location 1",IF($D$3="Location 2","Location 2","Location 11")),'RLB Index'!$C$1:$E$1,0))
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-23-2019
    Location
    Oakland
    MS-Off Ver
    Professional Plus 2016
    Posts
    10

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    Thank you Glenn. Works perfectly.

  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,891

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  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,340

    Re: INDEX-MATCH multiple criteria - with a variable value in a cell

    Quote Originally Posted by Glenn Kennedy View Post
    I think you maybe did change it... but not enough. It should ideally look like the words you'd use in a Google search for your solution.

    You can expect that further posts with weak titles will be locked until YOU make the required changes.
    I changed it (you will have seen my name in the post as the editor). I was waiting for further clarity on the issue before suggesting a further change, but you got there first.

+ 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: 7
    Last Post: 10-03-2019, 11:23 AM
  2. [SOLVED] Index/Match/Min/ABS Function needs to ignore one value in the index.
    By pronghorn in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 09-16-2019, 02:04 AM
  3. How to apply an Index.Match.Match function to all entries in a Listbox on a User Form
    By jason.drozd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-25-2018, 01:54 AM
  4. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  5. Problems applying INDEX-MATCH-MATCH function on other data
    By LennartB in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-13-2015, 05:33 AM
  6. [SOLVED] Stuck on Match function with #N/A; attempting to reverse Index/Match
    By Cappytano in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-10-2014, 06:39 PM
  7. Replies: 3
    Last Post: 06-17-2013, 12:37 PM

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