I need to compare a column from two different Excel sheets and return a value of true or false.
Excel – Compare a column from two different sheets and return true/false
microsoft excelworksheet-function
Related Solutions
If I understand your question correctly, you would like a formula that will read the first and last name on F1, locate the corresponding row on F2, and return the email address. This array formula will accomplish this. Make sure you press Ctrl+Shift+Enter when entering the formula because it is an array formula.
=INDEX('F2'!F:F, MATCH(A1&B1, 'F2'!A:A&'F2'!B:B, 0))
This assumes the following:
- Names are located on columns A and B on F1 and F2
- Email addresses are located in column F on F2
- Formula is entered into sheet F1
Note that the row numbers should be changed depending on where you enter the formula (the 1
's in "A1&B1
".
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.
Best Answer
Comparing the whole column:
or comparing a cell in the two sheets:
This will put TRUE in the cell where the formula is, if the two columns or cells are the same, and FALSE if they're different.