return data based on matching 2 criteria

Hi,

I'm trying to get Excel to do a complicated IF function, which I don't have a lot of experience with. I'm definitely open to other suggestions as well. Basically, I need Excel to return one value if two conditions are met in different columns of data. I've attached an example to clarify.

Sheet 1 contains reference information, which will be reference to complete the return column in sheet 2. I need a formula that will be entered into C2:C10 on Sheet 2 that will give the listed returns. It needs to verify that first that Sheet 2, A2 matches data from column A in Sheet 1, and then verify that Sheet 2, B2 matches data from column B in Sheet 1 before returning either "Active" or "Inactive" depending on the reference. Not sure if that makes sense, but basically I need the sheet to give the returns I've got listed in red on sheet 2, based on data from sheet 1. Thanks in advance!

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

(I have changed the title for you)

Try this, copied down...
=INDEX(Sheet1!$C$2:$C$17,MATCH(Sheet2!A2&Sheet2!B2,INDEX(Sheet1!$A$2:$A$17&Sheet1!$B$2:$B$17,0),0))

Two more ways. Both array entered in C2 and filled down.

Formula:

Please Login or Register  to view this content.

or

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.