Listing data based on a referneced criteria
 

I am trying to identify what function(s) to use to give me the data listed
in column B if column E, I & S meets my criteria.
e.g if column E = "NAME" and column I = "Batch 1" and column S = "No", then
return the entry in column B.

so on a different worksheet I would have a list of places that met the
criteria.

Any thoughts or has anyone done anything sinilar?

Many thanks
Mick

Listing data based on a referneced criteria
 

=INDEX(B2:B200,MATCH(1,(E2:E200="Johnson")*(I2:I20 0="Batch
1")*(S2:S200="No"),0))


entered with ctrl + shift & enter

replace the name, batch and yes/no with cells like E2:E200="Johnson and
instead use E2:E200=D1
that way you don't have to edit the formula when you change the criteria


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Mick" wrote in message
...
I am trying to identify what function(s) to use to give me the data listed
in column B if column E, I & S meets my criteria.
e.g if column E = "NAME" and column I = "Batch 1" and column S = "No",
then return the entry in column B.

so on a different worksheet I would have a list of places that met the
criteria.

Any thoughts or has anyone done anything sinilar?

Many thanks
Mick

Listing data based on a referneced criteria
 

Thanks for this, I have tried both options (referencing to a cell rather
than putting the name in the function).
I am getting a return of #N/A which seems to be linked to the INDEX part of
it, is it my data that is causing this problem, it is a text field with a
name in it.

Many thanks

"Peo Sjoblom" wrote in message
...
=INDEX(B2:B200,MATCH(1,(E2:E200="Johnson")*(I2:I20 0="Batch
1")*(S2:S200="No"),0))


entered with ctrl + shift & enter

replace the name, batch and yes/no with cells like E2:E200="Johnson and
instead use E2:E200=D1
that way you don't have to edit the formula when you change the criteria


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Mick" wrote in message
...
I am trying to identify what function(s) to use to give me the data listed
in column B if column E, I & S meets my criteria.
e.g if column E = "NAME" and column I = "Batch 1" and column S = "No",
then return the entry in column B.

so on a different worksheet I would have a list of places that met the
criteria.

Any thoughts or has anyone done anything sinilar?

Many thanks
Mick

Listing data based on a referneced criteria
 

make sure you press ctrl+shift+enter (not just enter) after edit the formula


"Mick" wrote:

I am trying to identify what function(s) to use to give me the data listed
in column B if column E, I & S meets my criteria.
e.g if column E = "NAME" and column I = "Batch 1" and column S = "No", then
return the entry in column B.

so on a different worksheet I would have a list of places that met the
criteria.

Any thoughts or has anyone done anything sinilar?

Many thanks
Mick

Listing data based on a referneced criteria
 

"Mick" wrote:
.. am getting a return of #N/A which seems to be linked to the INDEX part
of it, is it my data that is causing this problem, it is a text field with
a name in it.

It's probably the MATCH part of it. Any extra white spaces in either of the
3 source ranges, and/or in the 3 corresponding cells referenced, would throw
the MATCH off and return #N/A.

Assuming the 3 reference cells are E1, I1 and S1, try this variation of
Peo's suggestion which should cover all possibilities (array-entered as
before):
=INDEX(B2:B200,MATCH(1,(TRIM(E2:E200)=TRIM(E1))*(T RIM(I2:I200)=TRIM(I1))*(TRIM(S2:S200)=TRIM(S1)),0) )
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Listing data based on a referneced criteria
 

I have followed the excellent advice below but it doesn't appear to work, am
I using the correct formula for the result I want?
Out of 4 columns I want to check three different columns for a data match,
if all are true I would like the vallue in the 1st column to be returned,
this is a rext field and does not match anything in the other 3 columns that
I performed a match on.

Any advice
Thanks
Mick


"Mick" wrote in message
...
Thanks for this, I have tried both options (referencing to a cell rather
than putting the name in the function).
I am getting a return of #N/A which seems to be linked to the INDEX part
of it, is it my data that is causing this problem, it is a text field with
a name in it.

Many thanks

"Peo Sjoblom" wrote in message
...
=INDEX(B2:B200,MATCH(1,(E2:E200="Johnson")*(I2:I20 0="Batch
1")*(S2:S200="No"),0))


entered with ctrl + shift & enter

replace the name, batch and yes/no with cells like E2:E200="Johnson and
instead use E2:E200=D1
that way you don't have to edit the formula when you change the criteria


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)



"Mick" wrote in message
...
I am trying to identify what function(s) to use to give me the data
listed in column B if column E, I & S meets my criteria.
e.g if column E = "NAME" and column I = "Batch 1" and column S = "No",
then return the entry in column B.

so on a different worksheet I would have a list of places that met the
criteria.

Any thoughts or has anyone done anything sinilar?

Many thanks
Mick

Listing data based on a referneced criteria
 

"Mick" wrote:
I have followed the excellent advice below but it doesn't appear to work,
am I using the correct formula for the result I want?

Did you try the amended version posted ?
It should have worked
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Listing data based on a referneced criteria
 

Dear all especially Peo & Max
Sorry for the delay in responding, just to confirm the formulas posted
worked very well, in fact excellent.

Many many thanks for your time and assistance.
Mick


"Mick" wrote in message
...
I am trying to identify what function(s) to use to give me the data listed
in column B if column E, I & S meets my criteria.
e.g if column E = "NAME" and column I = "Batch 1" and column S = "No",
then return the entry in column B.

so on a different worksheet I would have a list of places that met the
criteria.

Any thoughts or has anyone done anything sinilar?

Many thanks
Mick

Listing data based on a referneced criteria
 

Max
Sorry for delay in responding, yes it worked very well, excellent in fact.

Many thanks for your time and assistance.

Mick

"Max" wrote in message
...
"Mick" wrote:
I have followed the excellent advice below but it doesn't appear to work,
am I using the correct formula for the result I want?

Did you try the amended version posted ?
It should have worked
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Listing data based on a referneced criteria
 

Glad to hear that, Mick !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mick" wrote in message
...
Max
Sorry for delay in responding, yes it worked very well, excellent in fact.

Many thanks for your time and assistance.

Mick