Lookup iferror, index/match errors

Copper Contributor

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 number, employee status etc are supposed to be filled automatically depending on the position table the employee is stored in (that is multiple tables searched depending on selection)

 

The formula I used after searching through countless forums is returning the error "you have entered too many/few arguments for this function." Please help me fix it.

10 Replies

@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")

Interesting @Sergei Baklan I've never come across the IFNA function before!

 

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

@evitajere 

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

No@Sergei Baklan 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!

@Sergei Baklan. 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 ..."

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

Replacing IFNA with IFERROR worked like magic @Sergei Baklan! Thank you so much! I'm using Office 2010 btw.

 

Let me work on things now. The formula was the missing piece! 

@evitajere , okay, great to know you sorted this out

Using the IFEEROR, INDEX and MATCH Formula
We want to get a product description in the cell F3, from the lookup table H3:I9, based on the Product ID 103 in the cell C3. If Product ID 103 is not found in the lookup table, we want to return “Description missing” in F3.

The formula looks like:

=IFERROR(INDEX($H$3:$I$9, MATCH(C3, $H$3:$H$9, 0), 2), “Description missing”)

The lookup_value parameter of the MATCH function is C3. The lookup_array is $H$3:$H$9, while the match_type is 0, as we want the exact match. The result of the MATCH function is the row_num parameter of the INDEX function. The array the range $H$3:$I$9. Finally, the value parameter of the IFERROR function is the result of the INDEX function, while the value_if_error is “Description missing”.

To apply the formula, we need to follow these steps:

Select cell F3 and click on it
Insert the formula: =IFERROR(INDEX($H$3:$I$9, MATCH(C3, $H$3:$H$9, 0), 2), "Description missing")
Press enter
Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

@Lewis-H 

For this concrete example I'd recommend

=IFERROR(INDEX($I$3:$I$9, MATCH(C3, $H$3:$H$9, 0)), “Description missing”)

Why to replicate the weakness of VLOOKUP()?