+ Reply to Thread
Results 1 to 19 of 19

Last item in a list with index and match

  1. #1
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Last item in a list with index and match

    So i need the value of column 1 displayed when two other items in a row match.
    Right now I can get the first matched row to display, but I cannot figure out how to display the last in a list.
    here is my formula: {=INDEX(Diesel!$A$36:$T$5000,MATCH(1,(Diesel!$G$36:$G$5000="T-64")*(Diesel!$H$36:$H$5000="release"),0),1)}

    Thanks!

  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: Last item in a list with index and match

    Try this:

    =LOOKUP(2,1/(Diesel!$G$36:$G$5000="T-64")*(Diesel!$H$36:$H$5000="release"),Diesel!$A$36:$A$5000)
    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
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    That formula was my first attempt, before I tried out the Index & Match method.
    The LookUp formula only looks for one of the last matching references and not both, even with the * added in.
    It will find the last entry with T-64 in it, and not the entry with "T-64" and "Release".
    Thank You for your help!!!

  4. #4
    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: Last item in a list with index and match

    But how do I know what's in the list and what you want to return? I'm not psychic!!!

    Attach a sample workbook.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Last item in a list with index and match

    It needs additional brackets:

    =LOOKUP(2,1/((Diesel!$G$36:$G$5000="T-64")*(Diesel!$H$36:$H$5000="release")),Diesel!$A$36:$A$5000)
    Rory

  6. #6
    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: Last item in a list with index and match

    Ah - good spot, Rory!

  7. #7
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    Ha got it!!!! The formula looks like this instead.
    =LOOKUP(2,1/(Diesel!$G$35:$G$5000="T-64")/(Diesel!$H$35:$H$5000="Release"),Diesel!$A$35:$A$5000)

    Thanks!

  8. #8
    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: Last item in a list with index and match

    That's very different!

    * gives an AND result

    / gives an OR result

    You said this:

    So i need the value of column 1 displayed when two other items in a row match.
    So what you really meant was this:

    So i need the value of column 1 displayed when ONE OF two other items in a row match.

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Last item in a list with index and match

    Quote Originally Posted by AliGW View Post
    * gives an AND result

    / gives an OR result
    Nope. I think you are thinking of + which would give an OR result.

  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: Last item in a list with index and match

    Yes, you are right - I was muddling it with +.

  11. #11
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    Just so I am understanding some of the vast excel formulas...
    what does / actually do or mean? especially in my formula because it does exactly what I want the formula to do.
    Which is: needing the value of column 1 displayed when two other items in a row match.

  12. #12
    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: Last item in a list with index and match

    Sorry - I might have confused issues here!

    The two formulae will return the same result (i.e. the last matching record in a list):

    Excel 2016 (Windows) 32 bit
    I
    J
    K
    L
    M
    2
    Tom
    1
    a
    8
    =LOOKUP(2,1/(I2:I10="John")*(K2:K10="a"),J2:J10)
    3
    John
    2
    a
    8
    =LOOKUP(2,1/(I2:I10="John")/(K2:K10="a"),J2:J10)
    4
    Harry
    3
    a
    5
    Tom
    4
    a
    6
    John
    5
    a
    7
    Harry
    6
    a
    8
    Tom
    7
    a
    9
    John
    8
    a
    10
    Harry
    9
    a
    Sheet: Sheet1

    The / is a divisor, * is a multiplier. Use the Evaluate Formula feature on the Formulas ribbon to see how the formula works out the result.

  13. #13
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    There is a different in results between the two formulas.
    If you switch the formula with the * in it so it looks like the example below you get a result of 9 not 8 because it looks for the first match.
    ie: =LOOKUP(2,1/(K2:K10="a")*(I2:I10="John"),J2:J10)
    but if you use / it will look for both and present the result.
    Last edited by comatose1978; 08-13-2018 at 02:50 PM.

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Last item in a list with index and match

    Still missing brackets:

    =LOOKUP(2,1/((K2:K10="a")*(I2:I10="John")),J2:J10)

    to ensure the two True/False arrays are multiplied first, before 1 is divided by the results (which will be an array of 1s and 0s).

  15. #15
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    Quote Originally Posted by rorya View Post
    Still missing brackets:
    I get the same result with and without the extra bracket.
    however I am using this code: =LOOKUP(2,1/(K2:K10="a")/(I2:I10="John"),J2:J10)
    Would you please explain to me why they are needed or not?
    I do not mean to challenge you, I do not want to run bad code that may cause issue in the future.

    Thank alot!!!
    Last edited by comatose1978; 08-13-2018 at 05:21 PM.

  16. #16
    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: Last item in a list with index and match

    I think in the case of the multiplication, it’s not a problem, as multiplications occur before divisions. In my example above, both formulae give the correct result, and neither has the extra brackets. The results are in column L in the table - both 8.

    The only way we are going to be able to resolve your issue is if you attach the workbook here and let us see what you have done wrong and/or why it isn’t working with your data.

  17. #17
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    Quote Originally Posted by AliGW View Post
    I think in the case of the multiplication, it’s not a problem, as multiplications occur before divisions. In my example above, both formulae give the correct result, and neither has the extra brackets. The results are in column L in the table - both 8.

    The only way we are going to be able to resolve your issue is if you attach the workbook here and let us see what you have done wrong and/or why it isn’t working with your data.
    My issue has been resolved, I was just asking a Roya a question.
    also your result in column L will not be 8 if you formula looks like this: =LOOKUP(2,1/(K2:K10="a")*(I2:I10="John"),J2:J10)
    Notice it is the * formula but with the queries reversed.

  18. #18
    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: Last item in a list with index and match

    Just add the extra pair of brackets and it will work.

  19. #19
    Registered User
    Join Date
    08-10-2018
    Location
    PDX
    MS-Off Ver
    365
    Posts
    10

    Re: Last item in a list with index and match

    Quote Originally Posted by AliGW View Post
    Just add the extra pair of brackets and it will work.
    <---- Mind Blown!!

    Thanks!

+ 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. Using Index Match to consolidate a list and exclude an item
    By StuartAllenNZ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2018, 11:31 PM
  2. [SOLVED] Find item(s) in a cell and match to item(s) in a list
    By seleseped in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2017, 12:06 PM
  3. [SOLVED] Match index value return all the corresponding line item
    By silambarasan.J in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-20-2016, 09:27 AM
  4. How do index match exact value in each item
    By Vandini.S in forum Excel General
    Replies: 9
    Last Post: 07-11-2015, 11:18 AM
  5. Item Match between TABS (Index,Match???)
    By jespo1351 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2014, 03:28 PM
  6. Replies: 9
    Last Post: 05-21-2011, 12:14 AM
  7. Index and match function for same item in the table
    By ronlau123 in forum Excel General
    Replies: 3
    Last Post: 05-15-2011, 02:11 AM

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