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>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top