#1
|
|||
|
|||
Data Compare and result as true or false in result sheet
Dear All,
I have a sheet Mdata and Bdata and third sheet is result sheet. I want to cross check data in Mdata sheet and data in Bdata sheet and result in result sheet as true or false in B4 and so on. for ex. Mdata sheet value i.e A4, B3, B4 MATCH WITH BDATA SHEET THEN MATCH TRUE OR FALSE VALUE IN IN RESULT SHEET B4 AND SO ON. Please find the attached sheet for ref. |
#2
|
||||
|
||||
In order to avoid array formulas, first add a helper column to BData to combine the employee ID and date.... so in H2 enter formula:
=A2&"_"&TRIM(TEXT(B2,"dd/mm/yyyy")) copied down. Then in Result B4 enter formula: =IFERROR(TRIM(INDEX(Bdata!$E:$E,MATCH($A4&"_"&TEXT (B$3,"dd/mm/yyyy"),Bdata!$H:$H,0)))=Mdata!B4,"") Copied down and across the table I added an IFERROR() to return a blank if the match is not found. You can replace the "" with FALSE if you want the word False returned instead. Note: Careful when entering data that you don't have leading/trailing spaces. I've had to use the TRIM() function to remove them virtually. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
One Cell that controlls spread sheet result button to change simple fomula result | RAH | Excel Programming | 5 | 03-31-2018 04:52 PM |
Converting a 1 or 0 into TRUE or FALSE? | ayupchap | Mail Merge | 1 | 04-25-2017 02:45 PM |
How To Move Focus From VLOOKUP Result to Report Sheet | swindon.expat | Excel | 2 | 04-10-2016 12:15 AM |
Mail merge result in separate world files instead of one file containing all the data | xdhbsh | Mail Merge | 3 | 12-24-2015 12:23 AM |
Lookup multiple values and compare different scenarios to get a specific result | mws | Excel | 5 | 05-24-2014 04:52 AM |