Sep 21 2020 05:05 AM
Sep 21 2020 05:05 AM
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.
Sep 21 2020 05:40 AM
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")
Sep 21 2020 11:43 PM
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!
Sep 22 2020 01:42 AM
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.
Sep 22 2020 05:44 AM
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")
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.