Find matching number for the result

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
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



ABCDEFGHIJKLMNOPQRSTUVWX
1Range#>R12345Result>A5B1.L2UA1A2.L1A1T5A3.T2A4.L1A5.T3A1B5.L1A3.T4L2UA3B3.L1A4L1A2.L2UA5.T1L2UA4B4B2.L2UA2
2numberID> 123456789101112131415
3Category>A73561
4Category>B21510138
5Category>L1A3118106
6Category>L2UA21591211
7Category>T115684

<colgroup><col><col><col><col span="5"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,890
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-questions/1078497-excel-formula-if-then-maybe.html

I present a UDF that mimics TEXTJOIN and you can use that.
 
Last edited:

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,049
Messages
5,484,398
Members
407,438
Latest member
DKrakken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top