CODEWITHSUNDEEP
excelfunctionmatch
Anonimowy Anonim
Anonimowy Anonim

Reputation: 35

Excel Match function as array formula serching multiple colums

I've got some problem with this function. I've Tried to find position some cell on the list consisted of two columns. My formula MATCH(A1;D1:E10;0) gives me an #N/D error, but when I change formula for each separate column like e.g. MATCH(A1;D1:D10;0) it works. Is three possibility to do this simultanously for both colums. Maybe some array formula?

Upvotes: 0

Views: 41

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Match will not work with 2 dimensional ranges.

You can use AGGREGATE to return the correct ROW:

=AGGREGATE(15,6,ROW(D1:E10)/(D1:E10 = A1),1)

enter image description here

Upvotes: 1

Related Questions