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.
 |  Next  |  Last
Pages: 1 2
Prev: Keep cell colour when linking
Next: comparing data