Need a formula to lookup data in rows

[Davey Boy]

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

 

[Bob Phillips]

=INDEX(B2:J2,MATCH("2007-11-30"+1,F2:N2,0))/A2

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernard Liengme]

This seems to do it:
=IF(ISERROR(MATCH(B2+1,F2:N2,FALSE)),NA(),INDEX(F2:N2,,MATCH(B2+1,F2:N2,FALSE)-4)/A2)
best wishes