Adding IFERROR or IF ISNA to an already complex formula

[MarioMac]

If using an ugly (sorry) but effective formula to look up data from a daily backorder report to add into a daily tracker that also pulls data from multiple worksheets. My formual is an Index and Match, but I also have to trim the data from my backorder report (it is data dumped from a system and has extra spaces so the part # is not recognized). So this is what the formula looks like:

{=INDEX('J:\OPNS\Power and OR\Project Tracker\[BackOrder.xlsm]Data'!$G$2:$G$316,MATCH($D3,TRIM('J:\OPNS\Power and OR\Project Tracker\[BackOrder.xlsm]Data'!$C$2:$C$316),0))}

The data in column G is in my backorder report and provides the backorder dollar value. D3 has my finished good number in my Tracker (the main spreadsheet that is pulling all this data), and column C in the backorder has the same finished good number (but needs the trim due to extra spaces).

Obviously every finished good is not on the back order report, so I'm wanting to add either IF ISNA or IFERROR (I'm using Office 2010) to this so that I don't have #N/A in my tracker. As soon as I add this in my formula doesn't work. Am I just trying to ask Excel to do too much?

I would greatly appreciate any advice to solve this issue.

Thank you

 

[Jonmo1]

Welcome to the board..

You can add IFERROR like this

=IFERROR(INDEX('J:\OPNS\Power and OR\Project Tracker\[BackOrder.xlsm]Data'!$G$2:$G$316,MATCH($D3,TRIM('J:\OPNS\Power and OR\Project Tracker\[BackOrder.xlsm]Data'!$C$2:$C$316),0)),"")


also, you might get away with doing the basic match this way without the need to use TRIM and Array Enter the formula

=IFERROR(INDEX('J:\OPNS\Power and OR\Project Tracker\[BackOrder.xlsm]Data'!$G$2:$G$316,MATCH("*"&$D3&"*",'J:\OPNS\Power and OR\Project Tracker\[BackOrder.xlsm]Data'!$C$2:$C$316,0)),"")

 

[MarioMac]

Thank you - for the quick response and the solution. I like the second option of getting rid of the trim. I hadn't used that before.

 

[Jonmo1]

Glad to help, thanks for the feedback.


Be carefull with that alternate method though..
Depending on what is actually in D3 and column C, it may provide false positives.

Like if D3 is "Hello"
And some cell in column C is "Well Hello There"
Then it would consider that a match.

 

[MarioMac]

Is there any way to use this but have the number formats different. In the project tracker the number format is 10 digits (5450850600), in my component list (where I want to match data), they are listed as 5450-850-600. I'm having to copy the data and convert the format every day, which takes away the automation I was trying to get.