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.
posted by spinturtle to Computers & Internet (6 answers total) 3 users marked this as a favorite
start with


posted by Prince_of_Cups at 2:36 PM on March 17, 2011

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]

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

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

Best answer: Ok, but only one value from A for each row?

In that'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

« Older Have card, need data   |   How to buy new carpet for a 2-bedroom house? Newer »
This thread is closed to new comments.