Lookup column value and find values in corresponding row

Re: Lookup column value and find values in corresponding row

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selection in B8. Hopefully, I understood you correctly.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B8")) Is Nothing Then Exit Sub
    Dim foundLetter As Range
    Dim week As Range
    Dim foundWeek As Range
    Set foundLetter = Range("B3:B5").Find(Target, LookIn:=xlValues, LookAt:=xlWhole)
    If Not foundLetter Is Nothing Then
        For Each week In Range("C8:C11")
            Set foundWeek = Range("C2:Q2").Find(week, LookIn:=xlValues, LookAt:=xlWhole)
            If Not foundWeek Is Nothing Then
                Cells(foundLetter.Row, foundWeek.Column) = week.Offset(0, 1)
            End If
        Next week
    End If
End Sub

Re: Lookup column value and find values in corresponding row

Hi Ingo_Ingo,

You can do what you're after with formulas. In order to achieve this, I have taken a couple of liberties:

1. In cells E8:E11, I have added the incremental ranking numbers 1-4, i.e. 1 in E8, 2 in E9, and so on.
2. In order to create unique numbers (that you don't see in the results), each number in your table is reduced by its column number divided by 100000. This gives us the ability to separately identify the otherwise same numbers in E3 and N3. In the results for search type A, week 3 would be listed ahead of week 12.

In cell C8, enter the formula:
=INDEX($C$2:$Q$2,1,MATCH(LARGE(OFFSET($B$2,MATCH($B$8,$B$3:$B$5,0),1,1,COUNTA($C$2:$Q$2))-(COLUMN($C$2:$Q$2)/100000),$E8),OFFSET($B$2,MATCH($B$8,$B$3:$B$5,0),1,1,COUNTA($C$2:$Q$2))-(COLUMN($C$2:$Q$2)/100000),0))
This is an array formula, and must be confirmed with SHIFT + CTRL + ENTER instead of just ENTER.

In cell D8, enter the formula:
=OFFSET($B$2,MATCH($B$8,$B$3:$B$5,0),$C8)
This is a standard formula that you should confirm with just ENTER.

You can then drag down the formulas to the remaining rows.

Hope this helps.