# Index Match formula problem • ### Question

• Good morning,

I have a formula that doesn't work:

=INDEX(1:1048576,MATCH(O1,A:E,0),MATCH(O2,5:5,0))

The issue seems to be because when trying to match my row, I have to look for my match in one single column. But as you can see I have 5 columns of possible matches.

Thanks,

Pete

Friday, November 6, 2015 2:07 PM

• This assumes that O1 is unique in A:E:

=INDEX(1:1048576,SUMPRODUCT((A:E = O1)*ROW(A:A)),MATCH(O2,5:5,0))

Otherwise, this will return the first value  - array enter using Ctrl-Shift-Enter:

=INDEX(1:1048576,MIN(IF(A:E = O1,ROW(A:A))),MATCH(O2,5:5,0))

Friday, November 6, 2015 3:33 PM

### All replies

• This assumes that O1 is unique in A:E:

=INDEX(1:1048576,SUMPRODUCT((A:E = O1)*ROW(A:A)),MATCH(O2,5:5,0))

Otherwise, this will return the first value  - array enter using Ctrl-Shift-Enter:

=INDEX(1:1048576,MIN(IF(A:E = O1,ROW(A:A))),MATCH(O2,5:5,0))

Friday, November 6, 2015 3:33 PM
• Brilliant, thanks :-)

Friday, November 6, 2015 5:39 PM