Multiple criteria index matching

I have spent hours trying to work this out, found articles online but they don't appear to work.

I have an excel document with data on two tabs and want the below formula to show me the team people are on if the dates and userid match on both forms called "Raised Source" & "Sheet1"

{=INDEX(Sheet1!F:F,MATCH('Raised Source'!E:E&'Raised Source'!F:F,Sheet1!A:A&Sheet1!B:B,0))}

This throws out a #N/A error

Help!!!

Firstly I assume that your data is in this format:

And that you are looking to perform the search using data from 'Raised Source'! to search through Sheet1! and return the team from Sheet1! as well.

The formula:

The formula will need to be entered as an array (once copy and pasted while still in the formula bar hit CTRL+SHIFT+ENTER)

=IFERROR(INDEX(Team,MATCH(1,(E1=TeamDate)*(F1=UserID),0)),"")

I have used named ranges to make the formula neater these are:

Team      =Sheet1!$F$1:$F$6
TeamDate  =Sheet1!$A$1:$A$6
UserID    =Sheet1!$B$1:$B$6

Please note that when using an array formula on an entire column calculations slow down considerably so it is best to avoid that and define the exact range you are searching through or overshoot it by a sensible amount if you expect additions.

The Logic:

=INDEX(array,row,[column]) - This formula will 'index' the array you specify, you can tell it a row number and column number you want returned (for example =INDEX(B3:C4,1,1) will return the value in the first row and first column of B3:C4 which is B3. We don't need to tell the formula a column if we just want the first column returned or if the data we are looking for results in is only 1 column wide.

This is where the formula comes in. We use the MATCH(lookup_Value,Lookup_array,[match_type]) formula to tell us what position (or what row in this case) the value we are looking for occurs.

Normal use of MATCH() would be matching exact cell contents within a range but as we are using an array formula I played a little trick by using MATCH(1,(E1=TeamDate)*(F1=UserID),0)

So, I have told the formula to find 1 and tell me the position that it occurs. 1 is returned when both criteria match as with Excel formula, TRUE represents 1 and FALSE represents 0. When this formula works through row by row, if both results match and return TRUE then the array will store 1*1 which is 1 and of course if either or both are FALSE then it will return 1*0, 0*1 or 0*0 all of which result in 0.

As the named ranges all start on the same row, the relative position (or how many times until a 1 appears) will always be the row number that you want the INDEX() formula to return.

IFERROR(value,value_if_error) - If no match is found, the formula will throw an error as it is unable to match 1 to the array that I have built. If this occurs I have told the formula that I would like it to return ,"") instead which is a blank cell. You can update this to any text you would prefer, perhaps ,"Not Found")?

The other IF() in the formula from the other answer was specific to that question as they only wanted to know that there was a match rather than return something from that row.

Output: