Find matching number for the result

B

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



[TABLE="class: grid, width: 1051, align: center"]
<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>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]X[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Range#>[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD]Result>[/TD]
[TD="align: center"]A5[/TD]
[TD="align: center"]B1.L2UA1[/TD]
[TD="align: center"]A2.L1A1[/TD]
[TD="align: center"]T5[/TD]
[TD="align: center"]A3.T2[/TD]
[TD="align: center"]A4.L1A5.T3[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]B5.L1A3.T4[/TD]
[TD="align: center"]L2UA3[/TD]
[TD="align: center"]B3.L1A4[/TD]
[TD="align: center"]L1A2.L2UA5.T1[/TD]
[TD="align: center"]L2UA4[/TD]
[TD="align: center"]B4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]B2.L2UA2[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]numberID> [/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Category>[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Category>[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Category>[/TD]
[TD="align: center"]L1A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Category>[/TD]
[TD="align: center"]L2UA[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Category>[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
351
Fuji
F
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
137
montecarlo2012
montecarlo2012
T
  • Solved
This seems like a numeric error to me, but does anybody have an explanation.
Replies
2
Views
292
TomCon
T
L
  • Solved
If a row contains more then 3 numbers
Replies
3
Views
157
Eric W
Eric W
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

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