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

evitajere

Copper Contributor

Sep 21, 2020

Interesting SergeiBaklan! I've never come across the IFNA function before!

Still, see the attached screenshot when I enter the formula. Its returning #NAME? instead. What could I be doing wrong?

SergeiBaklan

Diamond Contributor

Sep 21, 2020

evitajere

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

evitajere
Copper Contributor
Sep 22, 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 22, 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.