Lookup with conditionals for different ranges

**Diego1494** · 06-12-2013, 12:44 PM

Dear Reader,

I have got the following situation:
ExcelForum-PROBLEM.xlsx

And what I want to do is to create a function that will look for and output the "PLACE" for the first appearance of "0.2" in column "VALUE", but only in the range where DATE 1 and DATE 2 are the same. I need to be able to drag this function so the process can be repeated with every different date.

The result I am looking for is this one:
ExcelForum-SOLUTION.xlsx

Kind Regards,
Diego.

**FDibbins** · 06-12-2013, 12:59 PM

Hi and welcome to the forum

Try this, copied down...
=COUNTIFS($A$2:$A$28,E2,$C$2:$C$28,0.4)
I get different answers you what you gave though?

**Diego1494** · 06-12-2013, 01:36 PM

Hi DIBBINS!

Thank you for your response, and it almost worked. The thing is that instead of returning the number of times the number appears on that range,
I need a function to tell me the POSITION of the number in the range, hence the difference in the results, do you know how I could do this?

For that reason I placed the "Place " column, so the function can return the "Place" (or position) for the first time the 0.4 appears on the range.

Best Regards,
Diego O.

**FDibbins** · 06-12-2013, 02:07 PM

OK, try this array formula...
=INDEX($A$1:$D$28,MATCH(F2&0.4,$A$1:$A$28&$C$1:$C$28,0),4)
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.