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

Data structure is too complicated. For this one it could be

=IFNA(INDEX(INDEX('Dropdown Lists'!$A$1:$AH$16,0,MATCH([@Position],'Dropdown Lists'!$A$1:$AH$1,0)+2),
             MATCH([@Name],INDEX('Dropdown Lists'!$A$1:$AH$16,0,MATCH([@Position],'Dropdown Lists'!$A$1:$AH$1,0)),0),1
),"not found")

Employee Register.xlsx26.7 MB

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 ..."