Forum Discussion

Copper Contributor

Sep 21, 2020

Lookup iferror, index/match errors

In the Workers Register- employees are segmented according to worker type, department and role. Depending on the department selcted- employee names are selected from a list. Fields such as employee ...

Employee Register.xlsx25.4 MB

excel

SergeiBaklan

Diamond Contributor

Sep 21, 2020

evitajere

If you open the file I attached it also returns an error?

evitajere

Copper Contributor

Sep 21, 2020

NoSergeiBaklan your file doesn't return the same error...you're the real MVP!

I notice at the beginning of your formula, you have ..._xlfn.INFA. What does that do?

Also, the formula is meant to be used in other workbooks reading from the Register. Are you able to help me with creating any dummy workbook that references the Workers Register? I'll be most grateful!

evitajere
Copper Contributor
Sep 21, 2020
SergeiBaklan. I just read up on the prefix _xlfn:
"Cause. The Excel workbook contains a function that is not supported in the version of Excel that you are currently running. For example, you may have opened a ..."
- SergeiBaklan
  Diamond Contributor
  Sep 22, 2020
  evitajere
  
  On which version of Excel are you? Please check here https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461 for which ones IFNA is supported.
  
  Anyway, instead if it you may use IFERROR(), just replace IFNA on it.
  - evitajere
    Copper Contributor
    Sep 22, 2020
    Replacing IFNA with IFERROR worked like magic SergeiBaklan! Thank you so much! I'm using Office 2010 btw.
    
    Let me work on things now. The formula was the missing piece!