Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to lookup data and enter it in another WB | Excel Worksheet Functions | |||
Formula to lookup matching data | Excel Discussion (Misc queries) | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |