Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Pulling out data based on font colour | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Copying data from one worksheet to another based on criteria | Excel Discussion (Misc queries) | |||
Choosing data based on Match to several items | Excel Worksheet Functions |