I have re-created your spreadsheet, with the exception of the "combined" column because it isn't necessary if you were only using it to be able to match.
From what I understood, you have 2 columns on sheet1 that you want to match against 2 columns on sheet2. If they do match, you want to copy a column from sheet2 back to sheet1. This can be done using 2 IF() statements within Excel. Note that this will only work for sequential rows. You mentioned sheet1 has 1876 rows but sheet2 has 8785 rows; this will only match on those first 1876 rows.
Here are the two worksheets I have setup. They are close to yours.
As you can see in the pictures, I have made rows 2 and 3 the same in each sheet, and then I made the date and time not match in row 4, and only the time not match in row 5.
If both items match, it takes the information from column C on sheet 2 and shows it in column C on sheet 1, which I believe is what you're asking for.
The IF formula in Excel looks like this: "IF(Test,[Value if True],[Value if False])". So what we do is first check if your dates match. If they do, then we use a second test to see if your times match. If either one fails, then we know they don't match.
Here is the formula in C2:
=IF(A2=Sheet2!A2,IF(B2=Sheet2!B2,Sheet2!C2,"Time doesn't match"),"Date doesn't match")
To break down the formula, it says:
IF A2 from sheet 1 equals A2 on sheet 2 [IF(A2=Sheet2!A2], then also check IF B2 on sheet one equals B2 on sheet 2 [IF(B2=Sheet2!B2]. If they do match then put the contents of C2 from sheet 2 in to B2 [Sheet2!C2]. If they don't match at this point then put "Time doesn't match" in B2. If the initial date test hadn't matched then put "Date doesn't match" in B2.
First rule of rocket science: simple things are easier than complex things.
I recreated your problem on a single sheet,
so I wouldn’t have to use sheet names everywhere.
Columns A-C correspond to Columns A-C on your Sheet1
(a.k.a. 'Copy of Location to Location')
and Columns AA-AE correspond to Columns A-E on your Sheet2
(a.k.a. 'Travel Form 17-18').
I reduced your formula (which you use in Sheet2!E8) to
=IFERROR(INDEX(C:C, MATCH(AB8&AC8, A:A&B:B, 0)), "")
which I put into my AE8.
It’s a lot easier to understand when the clutter is stripped away.
And the logic is not rocket science.
If FROM&TO isn't in the “Location to Location” table,
we want to search for TO&FROM:
=IFERROR(INDEX(C:C, IFERROR(MATCH(AB8&AC8, A:A&B:B, 0), MATCH(AC8&AB8, A:A&B:B, 0))), "")
is the formula I have in cell AE8 in this screenshot:
We’re apparently using different versions of Excel.
I can’t say ArrayFormula(…) in mine (Excel 2013);
I just type Ctrl+Shift+Enter
after a formula to make it an array formula.
So I don’t know exactly how this ArrayFormula(…) works
(are you sure you need to use it twice in your formula?).
But here’s my solution (from above)
translated back into your sheet and column names:
=IFERROR(INDEX(C:C, IFERROR(MATCH('Travel Form 17-18'!B8&'Travel Form 17-18'!C8, 'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B, 0), MATCH('Travel Form 17-18'!C8&'Travel Form 17-18'!B8, 'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B, 0))), "")
I’ll let you figure out where in there you need to say ArrayFormula(…).
Best Answer
You can do this by entering the column argument in VLOOKUP as an array constant. For example:
Enter this in E2. Then Select E2:F2 and confirm by holding down ctrl + shift while hitting Enter.
The formula returns the array:
{"McPherson","Gildea"}and entering it exactly the way I described returns the results into the two different cells.You can then select E2:F2 and fill down as far as necessary.
Look at Excel HELP for information on Array Formulas and Array Constants.
Note that if you do this correctly, in the formula bar the formulas will appear the same in both E2 and F2; and also there will be braces {...} around the entire formula. Although you enter the braces when you type in the array constant
{3,4}within the formula, Excel will add the braces that appear around the entire formula, when you enter a formula with thectrl+shift+enterkey combination.