IFNA and index match, is it possible to nest another index match?

[pcorpz]

Currently I have this array formula in my SO table: I also have a similar formula in my PO table.

{=IFNA(INDEX(PO[Price],MATCH([@[SO Date]]&[@[User Name]]&[@[Item No.]]&[@[SO Qty]],PO[Posting Date]&PO[User Name]&PO[Item No.]&PO[Quantity],0)),"Not Found")}


What I'm hoping to accomplish is for those "Not Found', I want excel to do another lookup -- but restrict the lookup array to only those that have the "Not Found" value in my PO table (Column R). And for my lookup value to just be the just the Item No. column. Is this possible?

 

[Sam_D_Ben]

I guess instead of "Not Found" , Apply your required function. it should work.

 

[pcorpz]

I guess instead of "Not Found" , Apply your required function. it should work.

That’s what I need help on. instead of “not found” I’d like another lookup.. I don’t know how to write it.

 

[Sam_D_Ben]

I am not sure if this work, but please try

{=IFNA(INDEX(PO[Price],MATCH([@[SO Date]]&[@[User Name]]&[@[Item No.]]&[@[SO Qty]],PO[Posting Date]&PO[User Name]&PO[Item No.]&PO[Quantity],0)),INDEX(PO[Price],MATCH([@[Item No.]],PO[Posting Date]&PO[User Name]&PO[Item No.]&PO[Quantity],0))}

 

[pcorpz]

I am not sure if this work, but please try

{=IFNA(INDEX(PO[Price],MATCH([@[SO Date]]&[@[User Name]]&[@[Item No.]]&[@[SO Qty]],PO[Posting Date]&PO[User Name]&PO[Item No.]&PO[Quantity],0)),INDEX(PO[Price],MATCH([@[Item No.]],PO[Posting Date]&PO[User Name]&PO[Item No.]&PO[Quantity],0))}

HI Sam, I don't want the lookup array to be the entire table for the N/A.. I'd like to limit it to "Not Found" in PO[Sales Exact]. In PO[Sales Exact], I have the same formula applied (but with lookup value from SO table) and it shows "Not Found" for those without a match. --- I'd like to add that criteria for the lookup array... does that make sense?