Listing data based on a referneced criteria

[Mick]

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

 

[Peo Sjoblom]

=INDEX(B2:B200,MATCH(1,(E2:E200="Johnson")*(I2:I200="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]

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

 

[Guest]

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

 

[Max]

.. 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))*(TRIM(I2:I200)=TRIM(I1))*(TRIM(S2:S200)=TRIM(S1)),0))

---

 

[Mick]

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]

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]

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

Many thanks for your time and assistance.

Mick

 

[Max]

Glad to hear that, Mick !