+ Reply to Thread
Results 1 to 14 of 14

Lookup a value in a matrix and give back the relavant headline

  1. #1
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Lookup a value in a matrix and give back the relavant headline

    Hello Xelers,

    may I ask your help to get the correct function.

    In tab1 you can find 4 numbers in cells B2:B5. Next to the numbers (D2:D5) are yellow marked cells. In that cells the headline should be listed to which the number belongs to. In D2 you find the correct headline of the number in B2 as a sample.

    In tab2 you will find the matrix. The headline is in row B2:AQ2.
    The data is in cells B3:AQ102

    I was trying to use "index" and "sumproducts". But I failed... Somewhere I made a mistake

    Please help

    Thank you very much
    Thomas
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    Maybe just this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    Duplicate post deleted by author.
    Last edited by TMS; 04-18-2024 at 03:24 PM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    Duplicate post deleted by author.
    Last edited by TMS; 04-18-2024 at 03:25 PM.

  5. #5
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Re: Lookup a value in a matrix and give back the relavant headline

    Hello TMS,
    thank you very much... it is working nicely and a in fact nice short formula.
    Thomas

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    You're welcome. Thanks for the rep.

    Note: the formula does not actually require the matrix at all.



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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  7. #7
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Lookup a value in a matrix and give back the relavant headline

    Hello Xelers,

    may I ask your help to get the correct function.

    In tab1 you can find 4 numbers in cells B2:B5. Next to the numbers (D2:D5) are yellow marked cells. In that cells the headline should be listed to which the number belongs to. In D2 you find the correct headline of the number in B2 as a sample.

    In tab2 you will find the matrix. The headline is in row B2:AQ355.
    The data is in cells B3:AQ355

    I was trying to use the "let" function, but due to the nature of the numbers in the matrix a rounddown is not working properly.

    Please help

    Thank you very much
    Thomas
    Attached Files Attached Files

  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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Lookup a value in a matrix and give back the relavant headline

    Try this:

    =LET(h,'Tab2'!$B$1:$AQ$1,d,SUBSTITUTE('Tab2'!$B$2:$AQ$2,"<",""),v,--IFNA(TEXTBEFORE(d,"-"),0),XLOOKUP(B2,v,'Tab2'!$B$2:$AQ$2,"N/A",-1))
    Attached Files Attached Files
    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.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    Another option

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    All in one:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    @Ali: 4.89 is not present in the matrix. Not sure if this is intentional but there are gaps.

  12. #12
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Re: Lookup a value in a matrix and give back the relavant headline

    Hello Ali, hello TMS,
    thank you very much for your kind assistance. Yes, there are a few gaps in fact. It is intentional. Those missing data would never occur :-)
    However, it all works and I appreciate your help alot.
    Thomas

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Lookup a value in a matrix and give back the relavant headline

    You're welcome. Thanks for the rep.


    Hope for the best, plan for the worst

  14. #14
    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. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Lookup a value in a matrix and give back the relavant headline

    You're welcome.

+ 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. [SOLVED] Search multiple columns and give back corresponding values
    By MULTIVERSE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-11-2022, 09:49 PM
  2. [SOLVED] How can you give back a value when it might not be the first to be shown?
    By ellen2830 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-19-2022, 01:12 PM
  3. [SOLVED] Highlight active row but after leaving it give its previous color back
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-18-2017, 10:36 AM
  4. Vlookup to give back the greater number as result
    By Fredbugatti in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-13-2017, 02:58 PM
  5. [SOLVED] Lookup in a list of number with a headline
    By Traima in forum Excel General
    Replies: 2
    Last Post: 08-03-2005, 03:05 AM
  6. give back times of current month automatically
    By Markus Scheible in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2005, 11:06 AM
  7. [SOLVED] [SOLVED] Excel should be able to give back the colorcode of a cell via celi
    By Bart Schouw in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-07-2005, 06:06 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