CODEWITHSUNDEEP
excelexcel-formula
LafaMan
LafaMan

Reputation: 180

excel INDEX MATCH w/ multiple criteria to return only unique values

I have a sheet that i get from exporting a week worth of data from our employee attendance software.

I want to parse the list of employees and output that list to another sheet leaving only unique names. This part ive been able to accomplish. However because of the format of the exported data there are a lot of cells that contain data that i do not want parsed, Along with the employee name there is an adjacent field with employee numbers, What i now want to find out if i can do, is use the both fields as a criteria for the match. Trying to modify my original formula keeps telling me im missing arguments and I cant seem to figure out where im going wrong. Any help would be appreciated.

=INDEX(Sheet1!$C$5:$C$840;MATCH(0;COUNTIF($B$4:B4;Sheet1!$C$5:$C$840);0))

Thats what i currently have populating the list in sheet2, The list of names its indexing from are in Sheet1 C5:840. The employee IDs are in Sheet1 B5:840.

I cant sort it purely by employee id either because there is also alphanumeric date entries between days of the week. I can upload a sample for clarification if necessary

Upvotes: 0

Views: 853

Answers (1)

LafaMan
LafaMan

Reputation: 180

=IFERROR(INDEX(Sheet1!$C$5:$C$840; MATCH(1; (COUNTIF($B$4:B4;Sheet1!$C$5:$C$840)=0)*(IF(ISTEXT(Sheet1!$C$5:$C840);1;0)); 0));"")

I figured it out using ISTEXT instead. Just in case anyone comes across a similar problem

Upvotes: 2

Related Questions