Hi...

This is the example I have in a spreadsheet:

Cell A2 = 100
Cell B2 = 11/30/10
Cell H2 = 1,000
Cell L2 = 12/1/10

There can be other data in cells F2 to N2

What I need is a formula that will return 1,000/100 in cell D2...

This is how I'm thinking of structuring the formula in D2:

IF "B2+1" matches a value in F2:N2, return the data 4 cells before the
matching cell and divide that by A2...

In other words - using my example above:
IF (11/30/10 plus 1) matches a value from F2 to N2 This finds
"12/1/10" in Cell L2 ... Then returns the data in the cell that's 4 cells
before 12/1/10 (or cell H2) and divide it by A2...

I hope I've made myself clear...

Thanks in advance for your help! ...

Dave

Try something like this:

D2: =IF(COUNTIF(F2:N2,B2+1),INDEX(F2:N2,1,MATCH(B2+1,F 2:N2,0)-4)/A2,"no
match")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Davey Boy" wrote:

Hi...

This is the example I have in a spreadsheet:

Cell A2 = 100
Cell B2 = 11/30/10
Cell H2 = 1,000
Cell L2 = 12/1/10

There can be other data in cells F2 to N2

What I need is a formula that will return 1,000/100 in cell D2...

This is how I'm thinking of structuring the formula in D2:

IF "B2+1" matches a value in F2:N2, return the data 4 cells before the
matching cell and divide that by A2...

In other words - using my example above:
IF (11/30/10 plus 1) matches a value from F2 to N2 This finds
"12/1/10" in Cell L2 ... Then returns the data in the cell that's 4 cells
before 12/1/10 (or cell H2) and divide it by A2...

I hope I've made myself clear...

Thanks in advance for your help! ...

Dave

Ron,
Thank you sooooo much! ...
I would never have come up with that myself...
Aloha! ...
dave...

?B?Um9uIENvZGVycmU=?=
wrote in :

Try something like this:

D2:
=IF(COUNTIF(F2:N2,B2+1),INDEX(F2:N2,1,MATCH(B2+1,F 2:N2,0)-4)/A2,"no
match")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP