Determining if a number got hit before another

[rhhince]

I have a spreadsheet with data from forex. I have two numbers side by side. Cell P10 is .7851 and cell Q10 is .7821. From a list of numbers from cell D11:E110, I would like to know which number got hit first. Help would be appreciated. Thanks.

 

[Ron Rosenfeld]

I have a spreadsheet with data from forex. I have two numbers side by side. Cell P10 is .7851 and cell Q10 is .7821. From a list of numbers from cell D11:E110, I would like to know which number got hit first. Help would be appreciated. Thanks.

Can the numbers in P10 and Q10 occur in either Column D or E?
Does "hit first" mean the same as the first row in which it appears?

Some combination of Match functions, possibly a lookup, should do the trick, but answers to the above questions will help in crafting an applicable solution.

 

[rhhince]

Can the numbers in P10 and Q10 occur in either Column D or E?

Does "hit first" mean the same as the first row in which it appears?



Some combination of Match functions, possibly a lookup, should do the trick, but answers to the above questions will help in crafting an applicable solution.

Yes. As it goes down the rows, which number got hit first from either column.

 

[Ron Rosenfeld]

Yes. As it goes down the rows, which number got hit first from either column.

This is one approach. Replace ColD and ColE with D11110 and E11:E110 respectively.

This formula must be **array-entered**:


=IFERROR(IF(MIN(IFERROR(MATCH(P10:Q10,ColD,0),9E+307))
<MIN(IFERROR(MATCH(P10:Q10,ColE,0),9E+307)),INDEX(
ColD,MIN(IFERROR(MATCH(P10:Q10,ColD,0),9E+307))),
INDEX(ColE,MIN(IFERROR(MATCH(P10:Q10,ColE,0),9E+307)))),"")

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[rhhince]

This is one approach. Replace ColD and ColE with D11110 and E11:E110 respectively.



This formula must be **array-entered**:





=IFERROR(IF(MIN(IFERROR(MATCH(P10:Q10,ColD,0),9E+307))

<MIN(IFERROR(MATCH(P10:Q10,ColE,0),9E+307)),INDEX(

ColD,MIN(IFERROR(MATCH(P10:Q10,ColD,0),9E+307))),

INDEX(ColE,MIN(IFERROR(MATCH(P10:Q10,ColE,0),9E+307)))),"")



----------------------------------------



To **array-enter** a formula, after entering

the formula into the cell or formula bar, hold down

<ctrl><shift> while hitting <enter>. If you did this

correctly, Excel will place braces {...} around the formula.

I will give it a shot. Thanks.