none
Index Match formula problem RRS feed

  • 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.

    Can someone please help me make this work?

    Thanks,

    Pete

    Friday, November 6, 2015 2:07 PM

Answers

  • 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