# Need formula: One event occurs earlier than another in table

#### TOguy

##### New Member
Hi there,

Hoping someone can help me out with my example below. I want to know if "Billy" had an apple before he had either an orange or banana first using the table below. (side note: I've generated the table using pivot table based on the raw data - not sure if that changes anything). A simple "yes", "no" response would suffice.
Thanks so much for any help

Person Date Apple Orange Banana
Billy Jan/1/2015 - 4 -
Billy Feb/1/2015 - 4 -
Billy Mar/1/2015 4 - -
Billy Apr/1/2015 4 - -
Billy May/1/2015 - - 4
Billy Jun/1/2015 - - 4

Last edited:

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi. As long as there is only one fruit per day then this works with your table in A1:E7

It needs array entering so when you place it in a cell press CNTL-SHIFT then ENTER. Not just ENTER. There may be a simpler way!

=IF(VLOOKUP(MIN(IF(\$A\$2:\$A\$100="Billy",\$B\$2:\$B\$100)),\$B\$2:\$E\$100,MATCH("Apple",\$B\$1:\$E\$1,0),0)>0,"Yes","No")

Thanks very much for the formula. I've tried putting it in column F and doing the array entering, but it's just returning #N/A results when I drag the formula down from F2:F7. Am I putting it in the wrong spot?

Thanks!

Thanks very much for the formula. I've tried putting it in column F and doing the array entering, but it's just returning #N/A results when I drag the formula down from F2:F7. Am I putting it in the wrong spot?

Thanks!

Are you getting a result in F1? Why would you drag it down? You asked if "Billy" had an apple before he had either an orange or banana. That answer would always be the same regardless so it doesn't need to be dragged down.

No, #N/A in F1 as well. You're right, I don't need to drag it down. Just wasn't sure if that's how it was structured so I tried it with my fingers crossed!
Any ideas? Thanks!

First check for array enter. Look for curly brackets around your formula in the formula bar {}. Then does any cell in A2:A100 equal "Billy". Then does any cell in B1:E1 equal "Apple"?

Try these:

=COUNTIF(A2:A100,"Billy")
=COUNTIF(B1:E1,"Apple")

Do any of them equal 0?

Yes, it has the curly brackets around the formula. The "=COUNTIF" formulas both return a result ("6" for Billy & "1" for apple)
Any way I can send you an email with the excel file, if possible? I feel like I'm missing something really simple here...
Thanks!

Only other thing is that your dates are not real dates. If they aren't you will need to change them to real dates. With the best will in the world excel doesn't know Jan/1/2015 is earlier than Feb/1/2015 if they are text entries.

Bingo! That was the issue - switched to "date" from "text" and it worked right away. Thanks very much!!

Replies
4
Views
341
Replies
1
Views
309
Replies
3
Views
383
Replies
3
Views
231
Replies
0
Views
406

1,203,065
Messages
6,053,327
Members
444,653
Latest member
Curdood

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back