Return Text if True based on multiple criteria

**JonnyMa** · 06-20-2013, 07:10 PM

I am using Excel 2007/2010.

I would like to know if there is a formula that would return text values if TRUE based on given criteria.

On sheet 1, I have a tracker that tracks several bits of data.

On sheet 2, I would like to analyze the data contained on sheet 1 based on certain criteria.

I have attached a simple sample excel file of what I am looking for.

Sheet 1 has 3 columns
A: Date
B: Region
C: Name

Sheet 2 has 3 fields of a form
C1: Region (entered manually)
C2: Date (entered manually)
C4: Name (return name if date = C2 and region = c1)

Thanks in advance for your help!

**Ace_XL** · 06-20-2013, 07:16 PM

use this array formula

=INDEX(Sheet1!$C$2:$C$13,MATCH(1,(Sheet1!$B$2:$B$13=C1)*(Sheet1!$A$2:$A$13=C2),0))

Confirm with Ctrl+Shift+Enter and not jsut Enter

**FDibbins** · 06-20-2013, 07:19 PM

Try this array formula...
=INDEX(Sheet1!$C:$C,MATCH(Sheet2!$C$1&Sheet2!$C$2,Sheet1!$B:$B&Sheet1!$A:$A,0),1)

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**FDibbins** · 06-20-2013, 07:21 PM

Or this non-array formula with a helper column...

in sheet1 D2, copied down...
=B2&A2
Then on sheet2...
=INDEX(Sheet1!$C:$C,MATCH(Sheet2!$C$1&Sheet2!$C$2,Sheet1!$D:$D,0),1)
entered in the regular way

**Tony Valko** · 06-20-2013, 07:27 PM

Here's another one...

Array entered**:

=INDEX(Sheet1!C2:C13,MATCH(C1,IF(Sheet1!A2:A13=C2,Sheet1!B2:B13),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**FDibbins** · 06-20-2013, 07:35 PM

1 of the things I LOVE about excel, there is never 1 right way to do something. 4 different ways to do a fairly simple task

**JonnyMa** · 06-20-2013, 07:45 PM

Thanks Ace_XL and FDibbins!

Both worked on the sample sheet, but only FDibbins' method worked on my actual tracker.

Either way, thank you both for your quick replies!

**Tony Valko** · 06-20-2013, 07:49 PM

Originally Posted by FDibbins

there is never 1 right way to do something.

As long as they get the correct result they're all the right way.

Now, if you want to know which way is the best way then that's a different story!

**FDibbins** · 06-20-2013, 08:33 PM

Happy to help and thanks for the feedback

@ Tony, the best way is the 1 the OP understands and is able to re-create easily, I would say?

**Tony Valko** · 06-20-2013, 08:53 PM

Originally Posted by FDibbins

@ Tony, the best way is the 1 the OP understands and is able to re-create easily, I would say?

Where I come from that's called dumbing it down.

Hard to know how many OPs are interested in learning or do they just want to get an answer.

For those that are here to learn we owe it to them to offer the best solution. At least, that's how I approach things.

**JonnyMa** · 06-21-2013, 02:46 PM

I am definitely interested in learning about the formulas, but at the same time, I do have deadlines and a tight schedule to maintain. So for me, the best way is the one where I can understand it quickly and re-create the results easily.

Thanks to you all for helping!

**Tony Valko** · 06-21-2013, 06:57 PM

You're welcome. Thanks for the feedback!

Return Text if True based on multiple criteria

LinkBack

Thread Tools

Rate This Thread

Display

Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Re: Return Text if True based on multiple criteria

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions