Yes/No Result when looking up data in a Matrix on separate worksheet

[SMHOWARD]

Hello Mr Excel Users!

New here so apologies ahead of time if I miss something but my brain is a bit fried trying to figure out how to do this.

So I have a Skills Matrix where I am trying to verify if a few hundred users are assigned specific skills with a Yes or No result based on a lookup from a table similar to below.
There are no column headers here, just the users' names to the left and which skills they have in the proceeding cells. There are duplicates of users names but the row may have different skills than the line above it with the same user.
We'll call this WorkSheetA

What I am trying to do is the following. On a separate work sheet, WorkSheetB, we have all the possible skills listed as headers. In WorkSheetB I would like to check if the User in A2 has the Skill in B1 based on the Matrix from WorkSheetA. I want just a simple Yes or No if the skill appears in the matrix on WorkSheetA.

I just can't wrap my head around the IF's and AND's here combined with needing to some sort of Lookup function.
It needs to be able to be dragged down and across as well as there are 150 users and 430 possible skills they could have.

Is this possible in Excel or do we need a more advanced data analytics tool?

Thank you for looking into my issue!

 

[Sufiyan97]

Hi & Welcome to MrExcel!

Try

Search Multiple Text with Sumproduct.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	User							User	Skill 1	Skill 2	Skill 3	Skill 4	Skill 5	Skill 6	Skill 7	Skill 8	Skill 9
2	Mickey	Skill 4	Skill 1	Skill 5	Skill 3			Mickey	Yes	No	Yes	Yes	Yes	No	No	No	No
3	Donald	Skill 8	Skill 9	Skill 5	N/A			Donald	No	No	No	No	Yes	No	No	Yes	Yes
4	Goofy	Skill 6	Skill 7	Skill 2	N/A			Goofy	No	Yes	No	No	No	Yes	Yes	No	No
5	Minnie	N/A	N/A	N/A	N/A			Minnie	No	No	No	No	No	No	No	No	No
6
7
8
Sheet1

Cell Formulas
Range	Formula
I2:Q5	I2	=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$2:$E$5,I$1)/($A$2:$A$5=$H2))),"Yes","No")

Note:
You will need to put N/A, where the data is blank like I have put in row 5 and column E.
Otherwise you will get wrong results.

 

[DRSteele]

Welcome to the Forum.

This kind of algorithm might help.

MrExcel posts19.xlsx
	A	B	C	D	E	F	G	H	I	J
1	data
2	a	4	1	5	3
3	b	8	9	5
4	b	6	7	2
5	c	1	4	6	9
6	d	2	4	3
7	e	1
8	c	9	8	4
9	a	7		1
10
11	matrix output
12		1	2	3	4	5	6	7	8	9
13	a	yes	no	yes	yes	yes	no	yes	no	no
14	b	no	yes	no	no	yes	yes	yes	yes	yes
15	c	yes	no	no	yes	no	yes	no	yes	yes
16	d	no	yes	yes	yes	no	no	no	no	no
17	e	yes	no	no	no	no	no	no	no	no
Sheet40

Cell Formulas
Range	Formula
B12:J12	B12	=TRANSPOSE(SORT(UNIQUE(TOCOL(B2:F9,1))))
A13:A17	A13	=UNIQUE(A2:A9)
B13:J17	B13	=IF(SUM(--($A13&B$12=$A$2:$A$9&$B$2:$F$9)),"yes","no")
Dynamic array formulas.

 

[Z51]

Perhaps a VBA option...

Sub SMH()
Dim i As Long, j As Long, k As Long, arr() As Variant, lastrowA As Long, lastcolA As Long, wsA As Worksheet
Dim wsB As Worksheet, lastcolB As Long, lastrowB As Long, x As Long, y As Long

Set wsA = Sheets("WorkSheetA")
Set wsB = Sheets("WorkSheetB")
lastrowA = wsA.Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(wsA.Range(Cells(2, 2), Cells(lastrowA, 500)).SpecialCells(xlCellTypeConstants, 2).Count)
MsgBox UBound(arr)
i = 1

For j = 2 To lastrowA
    lastcolA = wsA.Rows(j).Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    For k = 2 To lastcolA
        If wsA.Cells(j, 1).Value <> "" And wsA.Cells(j, k).Value <> "" Then
            arr(i) = wsA.Cells(j, 1).Value & wsA.Cells(j, k).Value
            i = i + 1
        End If
    Next k
Next j

lastrowB = wsB.Range("A" & Rows.Count).End(xlUp).Row
lastcolB = wsB.Rows(1).Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For x = 2 To lastrowB
    For y = 2 To lastcolB
        If Not IsError(Application.Match(wsB.Cells(x, 1).Value & wsB.Cells(1, y).Value, arr, 0)) Then
            wsB.Cells(x, y).Value = "Yes"
        Else
            wsB.Cells(x, y).Value = "No"
        End If
    Next y
Next x

End Sub

 

[SMHOWARD]

Wow I was not expecting so many quick responses! Thank you all! It looks like @Sufiyan97 response worked for what I needed but I'm going to keep these other options in my OneNote as alternates in case I get larger data sets or something. I really appreciate you all!

 

[Sufiyan97]

You're welcome.
Glad we could help.