I'm bring back a match between two different arrays in excel?

OR(E22:J22=$R$2:$R$17)

This brings back true, there is one and only one true, I just need to bring back that true value.


I tried : Match(true, OR(E22:J22=$R$2:$R$17), 0), to get the number, but it didn't seem to work.
LVL 5
syeager305Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Try this formula

=INDEX(E22:J22,MATCH(1,INDEX((COUNTIF($R$2:$R$17,E22:J22)>0)+0,0),0))

regards, barry
0
 
StephenJRCommented:
If there is only one, this array formula perhaps?

=SUM(IF(E22:J22=$R$2:$R$17,R2:R17))
0
 
StephenJRCommented:
I must say I'm surprised your (array) formula worked at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.