in [Functions]

Prev: Keep cell colour when linking
Next: comparing data
From: JStiehl on 3 Mar 2010 08:12 Data is dumped into Sheet1 and on Sheet2 I need a formula to pull out certain pieces of information. Here is what the data looks like: D.......P..R S 10 B R 3 10 B F 5 10 L R 10 10 L D 9 11 B R 20 11 B F 6 11 S R 20 I want to extract numbers from column S based on certain values in columns D, P and R. So, in order to give me the result of 5 from column S, here is what I'm using: =INDEX(S2:S41,MATCH(1,(D2:D41="10")*(R2:R41="F")*(P2:P41="B"),0)) I do Ctrl-Shift-Enter after entering the formula, but I get the #N/A message displayed. What is wrong with my formula? I am currently just trying to get the formula to work on Sheet1, but it will eventually need to be moved to Sheet2. Thanks so much for your help.
From: Max on 3 Mar 2010 08:24 One guess, this term > .. D2:D41="10" Put it as: D2:D41=10 (drop the quotes if its real numbers in col D) Any luck? hit the YES below -- Max Singapore --- "JStiehl" wrote: > Data is dumped into Sheet1 and on Sheet2 I need a formula to pull out certain > pieces of information. Here is what the data looks like: > > D.......P..R S > 10 B R 3 > 10 B F 5 > 10 L R 10 > 10 L D 9 > 11 B R 20 > 11 B F 6 > 11 S R 20 > > I want to extract numbers from column S based on certain values in columns > D, P and R. So, in order to give me the result of 5 from column S, here is > what I'm using: > > =INDEX(S2:S41,MATCH(1,(D2:D41="10")*(R2:R41="F")*(P2:P41="B"),0)) > > I do Ctrl-Shift-Enter after entering the formula, but I get the #N/A message > displayed. What is wrong with my formula? I am currently just trying to get > the formula to work on Sheet1, but it will eventually need to be moved to > Sheet2. > > Thanks so much for your help.
From: Teethless mama on 3 Mar 2010 08:25 Remove quotes arount the number 10 "JStiehl" wrote: > Data is dumped into Sheet1 and on Sheet2 I need a formula to pull out certain > pieces of information. Here is what the data looks like: > > D.......P..R S > 10 B R 3 > 10 B F 5 > 10 L R 10 > 10 L D 9 > 11 B R 20 > 11 B F 6 > 11 S R 20 > > I want to extract numbers from column S based on certain values in columns > D, P and R. So, in order to give me the result of 5 from column S, here is > what I'm using: > > =INDEX(S2:S41,MATCH(1,(D2:D41="10")*(R2:R41="F")*(P2:P41="B"),0)) > > I do Ctrl-Shift-Enter after entering the formula, but I get the #N/A message > displayed. What is wrong with my formula? I am currently just trying to get > the formula to work on Sheet1, but it will eventually need to be moved to > Sheet2. > > Thanks so much for your help.
From: Domenic on 3 Mar 2010 08:25 Since Column D contains numerical values, not text values, try removing the quotes from the first criteria... =INDEX(S2:S41,MATCH(1,(D2:D41=10)*(R2:R41="F")*(P2:P41="B"),0)) -- Domenic Microsoft MVP - Excel www.xl-central.com, "Your Quick Reference to Excel Solutions" "JStiehl" <JStiehl (a)discussions.microsoft.com> wrote in message news:D1056D54-FE61-46A3-8206-F6E9B864E766 (a)microsoft.com...> Data is dumped into Sheet1 and on Sheet2 I need a formula to pull out > certain > pieces of information. Here is what the data looks like: > > D.......P..R S > 10 B R 3 > 10 B F 5 > 10 L R 10 > 10 L D 9 > 11 B R 20 > 11 B F 6 > 11 S R 20 > > I want to extract numbers from column S based on certain values in columns > D, P and R. So, in order to give me the result of 5 from column S, here > is > what I'm using: > > =INDEX(S2:S41,MATCH(1,(D2:D41="10")*(R2:R41="F")*(P2:P41="B"),0)) > > I do Ctrl-Shift-Enter after entering the formula, but I get the #N/A > message > displayed. What is wrong with my formula? I am currently just trying to > get > the formula to work on Sheet1, but it will eventually need to be moved to > Sheet2. > > Thanks so much for your help.
From: JStiehl on 3 Mar 2010 08:30
Thanks so much for the help--it works perfect! "Max" wrote: > One guess, this term > > .. D2:D41="10" > Put it as: D2:D41=10 > (drop the quotes if its real numbers in col D) > Any luck? hit the YES below > -- > Max > Singapore > --- > "JStiehl" wrote: > > Data is dumped into Sheet1 and on Sheet2 I need a formula to pull out certain > > pieces of information. Here is what the data looks like: > > > > D.......P..R S > > 10 B R 3 > > 10 B F 5 > > 10 L R 10 > > 10 L D 9 > > 11 B R 20 > > 11 B F 6 > > 11 S R 20 > > > > I want to extract numbers from column S based on certain values in columns > > D, P and R. So, in order to give me the result of 5 from column S, here is > > what I'm using: > > > > =INDEX(S2:S41,MATCH(1,(D2:D41="10")*(R2:R41="F")*(P2:P41="B"),0)) > > > > I do Ctrl-Shift-Enter after entering the formula, but I get the #N/A message > > displayed. What is wrong with my formula? I am currently just trying to get > > the formula to work on Sheet1, but it will eventually need to be moved to > > Sheet2. > > > > Thanks so much for your help. |