Share via

Excel Nested IFNAs

Anonymous
2023-09-13T15:15:00+00:00

Hello

I have the following formula which works great, it takes a bunch of criteria and searches a master list for the matching part number.

Data is in the following cells

B5

B12

B16

B18

B3

Formula

=IFNA(INDEX(Master_Fittings!E2:E1504,MATCH(1,(B19=Master_Fittings!H2:H1504)

*(B5=Master_Fittings!B2:B1504)

*(B12=Master_Fittings!C2:C1504)

*(B16=Master_Fittings!D2:D1504)

*(B18=Master_Fittings!F2:F1504)

*(B3=Master_Fittings!A2:A1504)

,0)),"Please add fitting")

However

If B5 = "Plug" I want it to omit this line from the search criteria

*(B16=Master_Fittings!D2:D1504)

I guess its a nested IF of some sort ?

If B5 = "Plug" then use the below criteria

=IFNA(INDEX(Master_Fittings!E2:E1504,MATCH(1,(B19=Master_Fittings!H2:H1504)

*(B5=Master_Fittings!B2:B1504)

*(B12=Master_Fittings!C2:C1504)

*(B18=Master_Fittings!F2:F1504)

*(B3=Master_Fittings!A2:A1504)

,0)),"Please add fitting")

Else use this criteria

=IFNA(INDEX(Master_Fittings!E2:E1504,MATCH(1,(B19=Master_Fittings!H2:H1504)

*(B5=Master_Fittings!B2:B1504)

*(B12=Master_Fittings!C2:C1504)

*(B16=Master_Fittings!D2:D1504)

*(B18=Master_Fittings!F2:F1504)

*(B3=Master_Fittings!A2:A1504)

,0)),"Please add fitting")

Hope you can help

Thanks

Geoff

Microsoft 365 and Office | Excel | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-13T15:38:10+00:00

    You need an exact match to fire the NA (the FALSE in the MATCH function), and you can use a simple nested IF

    IF(B5="Plug",1,(B16=Master_Fittings!D2:D1504))

    in place of

    (B16=Master_Fittings!D2:D1504)

    =IFNA(INDEX(Master_Fittings!E2:E1504,MATCH(1,(B19=Master_Fittings!H2:H1504) *(B5=Master_Fittings!B2:B1504) *(B12=Master_Fittings!C2:C1504) *IF(B5="Plug",1,(B16=Master_Fittings!D2:D1504)) *(B18=Master_Fittings!F2:F1504) *(B3=Master_Fittings!A2:A1504),FALSE),0),"Please add fitting")

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-09-13T15:54:47+00:00

    Thanks Bernie ,worked perfectly

    Cheers

    Geoff

    Was this answer helpful?

    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more