0

Im having difficulty with the following, maybe you can help.

I need a formula to lookup the following details. I need it to do the following:

  • Search Column 2 for "A"
  • Filter the available rows by the criteria of column 3 having a value of "B1"
  • Filter the available rows by the most recent date from column A
  • Return the value from column D
DATE    A   A1  VALUE 1
DATE    C   D1  VALUE 1
DATE    B   F1  VALUE 1
DATE    A   A1  VALUE 1
DATE    C   P1  VALUE 1

https://i.stack.imgur.com/2bKoT.jpg

Thanks, Phil

Jerry
  • 70,495
  • 13
  • 100
  • 144
user2803971
  • 11

1 Answers1

0

You can try this formula:

=INDEX(D1:D6,MATCH(9^99,IF(IF((B1:B6="A")*(C1:C6="B1"),A1:A6)=MAX(IF((B1:B6="A")*(C1:C6="B1"),A1:A6)),ROW(D1:D6))))

This is an array formula however, so you'll have to use Ctrl+Shift+Enter to make it work as intended.

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • Thanks, it just returns N/A? – user2803971 Sep 22 '13 at 11:13
  • 1
    @user2803971 Maybe a google doc? [link](https://docs.google.com/spreadsheet/ccc?key=0Am9GG-rwdOL1dG81YXVqbEFyNXNpVWlHd0xlUHA4aEE&usp=sharing) You might want to remove your email address as this is a public site. – Jerry Sep 22 '13 at 11:29
  • Thanks Jerry, it works in google docs but as soon as i download it and open it in excel the formula returns #N/A. Any ideas? – user2803971 Sep 22 '13 at 11:32
  • @user2803971 Try this download file instead: [link](https://dl.dropboxusercontent.com/u/23298677/2803971.xlsx) Hmm, otherwise are your dates in column A really dates or text typed as date? – Jerry Sep 22 '13 at 11:41
  • 1
    @pnuts Oops, right. I took 'recent' for being smallest... derp moment there. Thanks! – Jerry Sep 22 '13 at 12:19
  • Thats almost working perfectly! Thanks very much. The only issue is, i need the value returned from the row with the most recent date. At the moment its returning the value from the row with the oldest date? – user2803971 Sep 22 '13 at 12:20
  • Oh, just saw your comment there. Ill make the change and see if it works – user2803971 Sep 22 '13 at 12:20
  • @user2803971 Yes, that's what pnuts have pointed out. Find `MIN` in the function and change it to `MAX`. – Jerry Sep 22 '13 at 12:21
  • Yep, thats fixed it. I cant thank you enough! Really big help, thanks very much – user2803971 Sep 22 '13 at 12:22