|
Need formula to lookup data in row
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 |
Need formula to lookup data in row
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 |
Need formula to lookup data in row
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 |
| All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com