1

I have the following data: enter image description here

I want to match the Date and Value col to the Mapped Date and Value colume:

I am doing this with:

=COUNTIFS($A:$A;E2;$B:$B;F2)

However that does not give me the Higher/Lower colume which I want to map. Basically it should look like the expected results col.

Any suggestions how to map this value?

I appreciate your answer!

PS.: One problem with just using:

=IF(COUNTIFS($A:$A;E8;$B:$B;F8);C8;"")

the value in row 20 gets marked with 0 because the if goes sequentially...

Aleks G
  • 56,435
  • 29
  • 168
  • 265
user2051347
  • 1,609
  • 4
  • 23
  • 34

1 Answers1

1

This is an extension of @Peter Albert's answer.

use this one in G2:

=IFERROR(INDEX($C$2:$C$19,MATCH(E2 & F2, $A$2:$A$19 & $B$2:$B$19, 0)),"")

and press CTRL+SHIFT+ENTER to evaluate it.

Community
  • 1
  • 1
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Thx for your answer! Is this also possible within the `COUNTIF` function? – user2051347 Mar 07 '14 at 15:03
  • 1
    no, I don't think so, Countif only can tell you whether this combitiation exist in columns A:B, but can't tell you where it is. – Dmitry Pavliv Mar 07 '14 at 15:04
  • Thx a lot for your answer! My problem is that when pulling the formula down I get: `You cannot change part of an array`. I know that I can apply it to one row(the way you described it), but how to apply it to many more? – user2051347 Mar 07 '14 at 15:06
  • 1
    there're two options for you: **1)** write this formula in G2, press CTRL+SHIFT+ENTER and than drag it down. **2)** select `G2:G23`, _with selected range_ enter formula in formula bar `=IFERROR(INDEX($C$2:$C$19,MATCH(E2:E23 & F2:F23,$A$2:$A$19 & $B$2:$B$19,0)),"")` and than press CTRL+SHIFT+ENTER – Dmitry Pavliv Mar 07 '14 at 15:11
  • 1
    Wow! I have to say simoco you are the best! Thx for your help and also learned a lot alonf the way! +1 – user2051347 Mar 07 '14 at 15:14