Value corresponding to the name

[shimaa01234]

HI,
I own this table
1 - There's column "A" Names
2 - There's column "B" Values
The cell "C1" drop-down list of names
I want when the name selection is brought corresponding value even if repeated Name?
I want the solution by formulas

M1	2	M1
M2	3	2
M1	5
M3	6
M4	8
M5	0
M1	4

<tbody>
</tbody>

 

[WarPigl3t]

Unfortunately I am unable to help you. That formula is not compatible with Excel 2007 or older and I don't know another way.

 

[Aladin Akyurek]

HI,
I own this table
1 - There's column "A" Names
2 - There's column "B" Values
The cell "C1" drop-down list of names
I want when the name selection is brought corresponding value even if repeated Name?
I want the solution by formulas

M1	2	M1
M2	3	2
M1	5
M3	6
M4	8
M5	0
M1	4

<TBODY>
</TBODY>

M1	2	M1
M2	3	Count
M1	5	3
M3	6	Value list
M4	8	2
M5	0	5
M1	4	4

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4664" width=131><TBODY>
</TBODY>

C1: M1

C3, just enter:

=COUNTIF(A1:A7,C1)

C5, control+shift+enter, not just enter, and copy down:

=IF(ROWS(C$5:C5)<=C$3,INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=C$1,
  ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(C$5:C5))),"")

 

[shimaa01234]

You did Mister "Aladdin"
You are a genius
But there are other desire
You could show these numbers so that change with the "M 1" corresponding to the number
In a single cell

 

[Aladin Akyurek]

You did Mister "Aladdin"
You are a genius
But there are other desire
You could show these numbers so that change with the "M 1" corresponding to the number
In a single cell

Would you specify "other desire"? What would be the outcome you want to see?

 

[shimaa01234]

I want to show the result in a single cell , not 3 cells
In other words , when Write "m1"
1 - appear in cell "C5" for example, NO 2
2 - Andm and choose "M1" change "C5" to the NO 5
3 - Andm and choose "M1" change "C5" to the NO 4

 

[Aladin Akyurek]

I want to show the result in a single cell , not 3 cells
In other words , when Write "m1"
1 - appear in cell "C5" for example, NO 2
2 - Andm and choose "M1" change "C5" to the NO 5
3 - Andm and choose "M1" change "C5" to the NO 4

I'm unable to follow. We don't have "C5" and NO's.

Are you looking for having

2, 5, 4

as result in a single cell, here in C5? Thus, not laid out in C5 followed by C6, and followed by C7?

 

[shimaa01234]

Yes Mister "Aladdin"
I want to "2,5,4" appear in any cell change "M1" corresponding to each number

 

[Aladin Akyurek]

Yes Mister "Aladdin"
I want to "2,5,4" appear in any cell change "M1" corresponding to each number

Add the following code in <ACRONYM title="visual basic for applications">VBA</ACRONYM> to your worbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now you can invoke...

D1, control+shift+enter, not just enter, and copy down:

=REPLACE(ACONCAT(IF($A$1:$A$7=$C1,", "&$B$1:$B$7,"")),1,2,"")

 

[shimaa01234]

Great Master "Aladdin"
But this is not required. "Unfortunately"
I mean:
1 - If I put the "M1" corresponding to the number "2", give me "2" in cell "D1", only "2"
2 - If I changed the "M1" corresponding to the number "5", give me "5" in cell "D1", only "5"
And so on for the number "4"
Unfortunately, but I know you're a genius and can do so easily

 

[WarPigl3t]

	A	B	C	D	E
1	M1	M12	2	2,	M1
2	M2	M23	3		2, 5, 4
3	M1	M15	5	5,
4	M3	M36	6
5	M4	M48	8
6	M5	M50	0
7	M1	M14	4	4,

<tbody>
</tbody>

I solved this in my dream last night. I woke up and saw you still haven't found a solution so here you go.
Create a new column B. Your old column B is now column C.

Insert this code into B1.

=CONCATENATE(A1,C1)

Copy that code down column B

Insert this code into D1.

=IFERROR(VLOOKUP($E$1&$C1,$B$1:$C$7,2,FALSE)&", ","")

Copy that code down column D

Insert this code into E2.

=REPLACE(IF(D1<>"",D1,"")&IF(D2<>"",D2,"")&IF(D3<>"",D3,"")&IF(D4<>"",D4,"")&IF(D5<>"",D5,"")&IF(D6<>"",D6,"")&IF(D7<>"",D7,""),LEN(IF(D1<>"",D1,"")&IF(D2<>"",D2,"")&IF(D3<>"",D3,"")&IF(D4<>"",D4,"")&IF(D5<>"",D5,"")&IF(D6<>"",D6,"")&IF(D7<>"",D7,""))-1,1,"")

I tested it. This code works.
Please reply to this thread as SOLVED if this solution worked for you. Also do me a personal favor and watch my YouTube video on excel work schedule builder.
Excel Schedule Builder Instructional Video - YouTube

Like my FaceBook page
https://www.facebook.com/pages/Excel-Schedule-Builder/336643339818224?ref=br_tf

Thank you!