Return Text if True based on multiple criteria

1. Return Text if True based on multiple criteria

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)

2. Re: Return Text if True based on multiple criteria

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

3. Re: Return Text if True based on multiple criteria

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.

4. Re: Return Text if True based on multiple criteria

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

5. Re: Return Text if True based on multiple criteria

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.

6. Re: Return Text if True based on multiple criteria

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

7. Re: Return Text if True based on multiple criteria

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!

8. Re: Return Text if True based on multiple criteria

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!

9. Re: Return Text if True based on multiple criteria

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?

10. Re: Return Text if True based on multiple criteria

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.

11. Re: Return Text if True based on multiple criteria

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!

12. Re: Return Text if True based on multiple criteria

You're welcome. Thanks for the feedback!

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1