• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with IFERROR and VLOOKUP formula

Dear Narayan,


Thank you! That works brilliantly.


Sorry, totally my fault. I should have realised you meant to put the formula in AO on the Master List, not the other forms!


One final question - I promise! - I note that, after row 516 on the Master List, as there are no names in the Columns A and B, the date comes up as 00/01/1900.


I really do need rows 516-750 to ensure that for the future, the formula is set up.


Is there a way of getting it so that the date is empty, rather than 00/01/1900?
 
Hi Lisa ,


Thank you.


The 00.01.1900 or 00/01/1900 is being displayed because we are not trapping missing data in the rows beyond 516 ; we can do this by including an IF statement for this purpose :


=IF(LEN($AO2)<10,"",IFERROR(OFFSET(INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$AO$2:$AO$200"),$AO2)>0),0))&"'!$F$2:$F$200"),MATCH($AO2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$AO$2:$AO$200"),$AO2)>0),0))&"'!$AO$2:$AO$200"),0)-1,),""))


Put this formula in J2 on the Master List tab , and copy it as far down as you wish. Remember to enter it as an array formula , using CTRL SHIFT ENTER.


What this does is check whether the length of the concatenated item in column AO is more than 10 ; I have chosen 10 as a reasonable number , since a concatenation of the Surname , Christian Name and Employee Number should be more than 10 characters in length. If such a combination is possible , you can probably reduce the 10 to 5.


Narayan
 
You must log in or register to reply here.
Back
Top