CODEWITHSUNDEEP
excelexcel-formula
giriokamat
giriokamat

Reputation: 3

Returning column header corresponding to matched value in separate sheet

Sheet 1

Name Gender
w 0
e 1
r 2
t 4
y 6
u 2
i NoMatch
q 1
w 1
e 1
r 2

Sheet 2 - Note sheet 2 has 2 "w" under Name column

Name Male 1 Female 2 other 3 other 4 other 5 Donotknow 6
w 0 0 0 0 0 0
w 1 0 0 0 0 0
a 0 0 0 0 0 1
q 1 0 0 0 0 0
r 0 1 0 0 0 0
e 1 0 0 0 0 0
t 0 0 0 1 1 0
y 0 0 0 0 0 1
u 0 1 0 0 0 0

I am using this formula in Sheet 1 under Gender:

=IFERROR(FILTER({1,2,3,4,5,6},INDEX(Sheet2!$B$2:$G$10,MATCH(A2,Sheet2!$A$2:$A$10,0),0)=1),"NoMatch")

Upvotes: 0

Views: 51

Answers (1)

JvdV
JvdV

Reputation: 75870

If you can live with the fact that a zero stands for 'No Match', then try: enter image description here

Formula in B1:

=BYROW(A2:A12,LAMBDA(a,MIN(IF((D2:D10=a)*E2:J10,SEQUENCE(,6),""))))

If not, then change too:

=LET(X,BYROW(A2:A12,LAMBDA(a,MIN(IF((D2:D10=a)*E2:J10,SEQUENCE(,6),"")))),IF(X,X,"No Match"))

Upvotes: 1

Related Questions