INDEX COLLECT not working

Ronald Anderson

I am unable to get a value returned using INDEX COLLECT. No matter what I try, I keep getting the IFERROR text "Not Found". I have tried using VLOOKUP as well with the same results.

I have deleted the reference sheet and reuploaded the data to a new sheet and still get the same results. Can anyone help me with this? Below is the current expression.

=IFERROR(INDEX(COLLECT({JobTitle}, {EmpID}, [Employee ID]@row), 1), "Not Found")

Thank you - Ron

Paul Newcome

They syntax looks right which leads me to believe you are not getting a match on the employee id. Are you able to provide some screenshots for reference?

Ronald Anderson

Yes, thank you.

Source sheet:

Reference sheet:

Christian G.

Can you send a screenshot where the employee ID exist in both table? With what I see here, your result are good because the employee Id from one page does not existe in the other one.

You should also try "Index Match" combo instead of Index collecte

=IFERROR(INDEX({JobTitle},MATCH([Employee ID]@row,{EmpID},0)), "Not Found")

Paul Newcome

The issue is that you are comparing text values (leading zeros) to numerical values (without leading zeros).

Insert a helper column on both sheets with this column formula:

=[Employee ID]@row + ""

This will convert everything into text values. Then you can compare/match on like data.

Ronald Anderson

Thank you for your help, but it did not work. I still get "Not Found".

Paul Newcome

Are you able to provide some updated screenshots with the helper columns in place, the exact formula you are now using, and showing an actual match that should be pulling through?

Ronald Anderson

Sure, here they are.

Formula: =IFERROR(INDEX(COLLECT({jobtitle}, {empidhelper}, [Emp ID Helper]@row), 1), "Not Found")

Source Sheet:

Reference Sheet: