Look Up Multiple Values in a Column based on single Criteria

[New Hope UMC]

In column B I need a formula that will look up Criteria value in
Column A from the list of values in Column J(ALL SCORES) and give me
the resulting teams from Column I (ALL TEAMS). The issue is when there
are multiple teams with the same score and being able to display both
of the teams. How would I do this??

Column A Column B
Scores TEAMS
A 303
B 302, 306
C 301
D 290
F 170


Column I Column J
ALL TEAMS ALL SCORES
303 A
302 B
306 B
301 C
290 D
170 F

 

[Don Guillett Excel MVP]

In column B I need a formula that will look up Criteria value in
Column A from the list of values in Column J(ALL SCORES) and give me
the resulting teams from Column I (ALL TEAMS). The issue is when there
are multiple teams with the same score and being able to display both
of the teams. How would I do this??

Column A                 Column B
Scores                     TEAMS
A                               303
B                         302, 306
C                               301
D                               290
F                               170

Column I             Column J
ALL TEAMS        ALL SCORES
303                         A
302                         B
306                         B
301                         C
290                         D
170                         F

Assumes A in col 1 and 302,306,400 in col B. If more than 3 modify to
suit
Puts in cols A & B
Sub getscoresSAS()
'put into columns
Columns("b").TextToColumns Destination:=Range("b1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(6, 1), Array(9, 1))

'line em up
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
lc = Cells(i, Columns.Count).End(xlToLeft).Column
If lc > 2 Then
'MsgBox lc
For j = lc To 3 Step -1
'MsgBox Cells(i, j)
Rows(i + 1).Insert
Cells(i, 1).Copy Cells(i + 1, 1)
Cells(i, j).Copy Cells(i + 1, 2)
Next j
End If
Next i
End Sub

 

[New Hope UMC]

Assumes A in col 1 and 302,306,400 in col B. If more than 3 modify to
suit
Puts in cols A & B
Sub getscoresSAS()
'put into columns
Columns("b").TextToColumns Destination:=Range("b1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(6, 1), Array(9, 1))

'line em up
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
lc = Cells(i, Columns.Count).End(xlToLeft).Column
If lc > 2 Then
'MsgBox lc
For j = lc To 3 Step -1
'MsgBox Cells(i, j)
Rows(i + 1).Insert
Cells(i, 1).Copy Cells(i + 1, 1)
Cells(i, j).Copy Cells(i + 1, 2)
Next j
End If
Next i
End Sub

Don,

Do you know of a way to accomplish this with formulas?

 

[Don Guillett Excel MVP]

Don,

Do you know of a way to accomplish this with formulas?- Hide quoted text -

- Show quoted text -

I have seen complicated ways to do this with formulas but I would only
use a macro.