MATCH the next item IF

[SCarroll]

Hello: I'm trying to create a condensed version of a table that pulls out variances and ignores all zeros. I've used an array with SMALL to successfully pull out a list of variances. However now I want to attach a customer name to each variance. The problem I've encountered is that if 2 customers have the same variance I don't know how to implement the IF statement so that it will go to the next match and return the next customer. I've tried various approaches and formulas without any luck.

I've attached a spreadsheet example where I have a list of customers in Column A, their Variances in Column B. In Columns D & E I want to set up a table where you only show the variances. Column D is the customer and E is their variance. Problem is that Customer A and E have the same variance of -1055 and so the table returns A for both instances. I would like the 2nd instance to show me customer E. Hopefully that makes sense.

Thanks!

[ELeGault]

Move away from the MATCH in this instance, instead FILTER your list

Your named range is on B2:B27 thus it says var_lookup in the formula... otherwise just remember to be consistent with your selected ranges(array) and filter ranges

Please Login or Register  to view this content.

[ELeGault]

2010 - you do not have Filter... okay so the fastest route would be add a helper column. Light weight as its a per line review...

Insert a column in front of A so your AB becomes BC. Then in A2 put this
=IF(C2=0,A1,A1+1)

Send the formula down to the length of your data. What you will see is something like this

1
1
1
2
3
3
3
etc...

Each time it hits a record that is not 0 it will add a number to the previous otherwise it will repeat the previous.

You can now match on that value based on position in your list

Index your range then match on the count of records from header to current position.
Then Match on COL reference also by getting a count of columns again from headers.

This formula will go into what is now your column E row 2 and throughout the light green table you have!
=IFERROR(INDEX($B$2:$C$27,MATCH(COUNTA($E$1:$E1),$A$2:$A$27,0),COUNTA($E$1:E$1)),"")

[nick.williams]

FILTER is probably the best option if you have a newer version of Excel. If not, try this:

in D2:

=IFERROR(INDEX(Customer_list,AGGREGATE(15,7,IF(var_lookup<>0,ROW(var_lookup),NA()),ROWS(E2:E$2))-1),"")

In E2:

=IFERROR(INDEX(var_lookup,AGGREGATE(15,7,IF(var_lookup<>0,ROW(var_lookup),NA()),ROWS(F2:F$2))-1),"")

Both copied down.

[Fluff13]

Another option

Please Login or Register  to view this content.

[SCarroll]

Got it! Thanks! Small correction to the formula - =FILTER(A2:A27,var_lookup<>0,"") and it worked!

[SCarroll]

Thanks! I have FILTER so that worked. But this is good to know as well because I have someone I help who is still using 2007.

[FlameRetired]

SCarroll glad you found solution.

If that formula works then your profile is not current. As ELeGault mentions in post #3 FILTER is not an Office 2010 function.

Please update your profile. It helps us help you.

Edit: It appears our posts crossed in the ether.

[SCarroll]

2010 - you do not have Filter... okay so the fastest route would be add a helper column. Light weight as its a per line review...

Insert a column in front of A so your AB becomes BC. Then in A2 put this
=IF(C2=0,A1,A1+1)

Send the formula down to the length of your data. What you will see is something like this

1
1
1
2
3
3
3
etc...

Each time it hits a record that is not 0 it will add a number to the previous otherwise it will repeat the previous.

You can now match on that value based on position in your list

Index your range then match on the count of records from header to current position.
Then Match on COL reference also by getting a count of columns again from headers.

This formula will go into what is now your column E row 2 and throughout the light green table you have!
=IFERROR(INDEX($B$2:$C$27,MATCH(COUNTA($E$1:$E1),$A$2:$A$27,0),COUNTA($E$1:E$1)),"")

This is also a clever solution! Sometimes I overlook the simple things. But the Filter did work! Thank you!

[ELeGault]

Glad it worked - Sometimes its the simple solutions that are over engineered. Who knew that the step we wanted to take was simply the next one!

Be sure to update your profile. If Filter is working then you are on a later version which is why I offered the second solution to begin with. It helps us (Anyone helping) tailor a solution to the environment you work in. This means it could also prevent very robust and exciting methods to be kept from you because you appear to be on an old instance!

Glad it worked though!

Cheers
- Eddie