stymied by excel
March 17, 2011 2:32 PM Subscribe
There has to be an answer for this, but I just can't figure it out. In Excel, I've got 5 columns (A-E). I need to return a list of values from column A if either column B or C equals 'foo', *and* if either column D or E equals 'bar'. What's the magical formula (or whathaveyou) that will make this happen? For bonus points, results need to appear on a separate tab than the source data.
asking on behalf of mrs spinturtle, I personally have no idea what I'm talking about.
What's the deal with list of values in column A? Do you need to find some sub-set of the contents and put them on another tab? Or is the 'list of values' one cell in column A?
The easy case, where you just want whatever's in cell A given your criteria is go to your new tab and put this in cell a1:
=IF(AND(OR(Sheet1!B1="foo",Sheet1!C1="foo"),OR(Sheet1!D1="bar",Sheet1!E1="bar")), A1, "whatever you should do if the criteria arent true")
posted by jeb at 2:36 PM on March 17, 2011 [1 favorite]
The easy case, where you just want whatever's in cell A given your criteria is go to your new tab and put this in cell a1:
=IF(AND(OR(Sheet1!B1="foo",Sheet1!C1="foo"),OR(Sheet1!D1="bar",Sheet1!E1="bar")), A1, "whatever you should do if the criteria arent true")
posted by jeb at 2:36 PM on March 17, 2011 [1 favorite]
I just saw results need to appear on a separate tab than the source data
and, yeah, jeb has the info on pointing at a cell on a different worksheet.
posted by Prince_of_Cups at 2:37 PM on March 17, 2011
and, yeah, jeb has the info on pointing at a cell on a different worksheet.
posted by Prince_of_Cups at 2:37 PM on March 17, 2011
Response by poster: mrs says: yes, I'm looking for a subset of contents. It's a 20k row spreadsheet, I need the value from A to appear on a new list (maybe 9k records) if the criteria for B/C and D/E above are met.
posted by spinturtle at 2:45 PM on March 17, 2011
posted by spinturtle at 2:45 PM on March 17, 2011
Best answer: Ok, but only one value from A for each row?
In that case...here's the easiest way to do this:
- put a formula like the one I described above on the second sheet, changing things like the Sheet1 to be the actual name of your sheet
- in the last pair of quotation marks (what gets returned when there's no match), take out all that stuff I wrote ("whatever you should...") and change it to "exclude".
- copy the formula
- paste it all the way down column A in the new sheet
- it will populate cells with either the value from column A or the word "exclude"
- select cell A1
- (This part depends on what version of Excel you are using, its either Tools|Data|Filter or the data ribbon, then clicking filter). Set up autofilter to include only rows where the value is NOT equal to "Exclude"
- select all the resulting data
- select a cell in a new sheet
- Edit|Paste Special...|Values to get just the data, nicely packed together, no more formulas
posted by jeb at 2:52 PM on March 17, 2011
In that case...here's the easiest way to do this:
- put a formula like the one I described above on the second sheet, changing things like the Sheet1 to be the actual name of your sheet
- in the last pair of quotation marks (what gets returned when there's no match), take out all that stuff I wrote ("whatever you should...") and change it to "exclude".
- copy the formula
- paste it all the way down column A in the new sheet
- it will populate cells with either the value from column A or the word "exclude"
- select cell A1
- (This part depends on what version of Excel you are using, its either Tools|Data|Filter or the data ribbon, then clicking filter). Set up autofilter to include only rows where the value is NOT equal to "Exclude"
- select all the resulting data
- select a cell in a new sheet
- Edit|Paste Special...|Values to get just the data, nicely packed together, no more formulas
posted by jeb at 2:52 PM on March 17, 2011
Response by poster: Thanks jeb! She says that sounds like what she's looking for, will try it out when she gets to work tomorrow and report back.
posted by spinturtle at 3:10 PM on March 17, 2011
posted by spinturtle at 3:10 PM on March 17, 2011
This thread is closed to new comments.
=IF(AND(OR(B8="foo",C8="foo"),OR(D8="bar",E8="bar")),"WIN","[null]")
posted by Prince_of_Cups at 2:36 PM on March 17, 2011