CODEWITHSUNDEEP
excelrangematch
BeginEnd
BeginEnd

Reputation: 343

excel match formula with range

A have problem with excel 2010 and formula using multiple values check:

   A       B      C
1 Test1   Test2 Test3
2 Name1   XXX    xxx
3 Name1   YYY   yyy
4 Name2   VVV    vvv
5 Name3   BBB    bbb
6   
7 Test1   Name1 
8 Test2  XXX    
9 Test3  #VALUE!  <--- formula =MATCH(1,(B7=A2:A5)*(B8=B2:B5), 0)

I always have the same error. I debuged it and the evaluation of 'A2:A5' generate error #VALUE! but when I select this and press F9 it expand to {"Name1";"Name1";"Name2";"Name3"}

Any ideas where can be the problem?

Thanks

Upvotes: 0

Views: 573

Answers (1)

barry houdini
barry houdini

Reputation: 46331

Your formula is an "array formula" so you need to confirm with CTRL+SHIFT+ENTER so that you get curly braces like { and } around the formula

alternatively you can add an INDEX function which means it can be entered as a regular formula, i.e.

=MATCH(1,INDEX((B7=A2:A5)*(B8=B2:B5),0),0)

Upvotes: 1

Related Questions