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

pcorpz

Active Member
Joined
Oct 29, 2004
Messages
324
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I guess instead of "Not Found" , Apply your required function. it should work.
 
Upvote 0
I am not sure if this work, but please try

Code:
{=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))}
 
Upvote 0
I am not sure if this work, but please try

Code:
{=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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top