Find matching number for the result
Results 1 to 4 of 4

Thread: Find matching number for the result
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find matching number for the result

    Hello All,

    I encountered a situation that I need your help with a formula to get the result as shown “result” range row J1:X1.

    The calculation based on weekly updated table on the left. If found a MATCH number from the “numberID” row J2:X2 in the Category row “A, B, L1A, L2UA, and T” , then the result is combined the Category name in B column with the “Range#” number.

    For example in the table below:
    The result found in "J1" based on numberID “1” in "J2". If found "1" matching on the left table as in "G3", then result is A5 by combining Category “A” with Range# 5.

    If multiple matching results found, then concatenated the result separating by period for each matching number as shown in "O1".

    Thank you all for the help.
    BDT



    A B C D E F G H I J K L M N O P Q R S T U V W X
    1 Range#> R 1 2 3 4 5 Result> A5 B1.L2UA1 A2.L1A1 T5 A3.T2 A4.L1A5.T3 A1 B5.L1A3.T4 L2UA3 B3.L1A4 L1A2.L2UA5.T1 L2UA4 B4 B2.L2UA2
    2 numberID> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
    3 Category> A 7 3 5 6 1
    4 Category> B 2 15 10 13 8
    5 Category> L1A 3 11 8 10 6
    6 Category> L2UA 2 15 9 12 11
    7 Category> T 11 5 6 8 4

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find matching number for the result

    If you have Excel 365 with the TEXTJOIN function, put this formula in J1:

    =TEXTJOIN(".",TRUE,IF($C$3:$G$7=J2,$B$3:$B$7&$C$1:$G$1,""))

    and confirm it by pressing Control+Shift+Enter, not just Enter. Then drag it to the right. If you don't have that, then you'll need some flavor of VBA. For example, if you go to post 6 in this thread:

    https://www.mrexcel.com/forum/excel-...hen-maybe.html

    I present a UDF that mimics TEXTJOIN and you can use that.
    Last edited by Eric W; Aug 23rd, 2019 at 05:51 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Oct 2018
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find matching number for the result

    Eric,

    Just got a chance to test your formula with the Excel 365. It worked perfectly with the expected results.

    I really appreciate your help with this formula.

    BDT

  4. #4
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,472
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Find matching number for the result

    Great! Glad to hear it. Thanks for the feedback.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •