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.

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.

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.

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" wrote in message
...
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.

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.

Thanks! It works great!

"Teethless mama" wrote:

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.

welcome, good to hear
--
Max
Singapore

"JStiehl" wrote in message
...
Thanks so much for the help--it works perfect!