Help with a long IF,ISNA,INDEX,MATCH formula

patrik116

New Member
Joined
Dec 14, 2018
Messages
2
I have this very very big list of almost 700K rows and about 30 columns of data. I have a somewhat working formula to do what I want but it doesn't really work 100% of the time.

The data is of all purchases done during a set period and I have a formula that determines what type of purchase it is based on 5 categories( lets call them difficulties 1, 2, 3, 4 and 5). Now as each user can be on present in a lot of rows I need to find our which is their highest level of difficulty in the list.

The data for each line looks like this (row 1 and 3 the top row with headers 2 and 4 are the data for row 2):
User ID (G)X (H)X(I)X (J)X (K)X (L)X (M)X (N)X (O)X (P)Difficulty (Q)Combination G2+Difficulty 1 (R)Combination G2+Difficulty 2 (S)Combination G2+Difficulty 3 (T)Combination G2+Difficulty 4 (U)Combination G2+Difficulty 5 (V)Combination G2+ Q2 (W)
Test1xxxxxxxxxDif4Test1-Dif1Test1-Dif2Test1-Dif3Test1-Dif4Test1-Dif5Test1-Dif4
IF(R2=W2;G2;"0") (X)IF(S2=W2;G2;"0") (Y)IF(T2=W2;G2;"0") (Z)IF(U2=W2;G2;"0") (AA)IF(V2=W2;G2;"0") (AB)Formula to determine highest difficulty (AC)X (AD)X (AE)Name of Difficulty 1 (AF)Name of Difficulty 2 (AG)Name of Difficulty 3 (AH)Name of Difficulty 4 (AI)Name of Difficulty 5 (AJ)
000Test10Dif1xxDif1Dif2Dif3Dif4Dif5

<tbody>
</tbody>


<tbody>
</tbody>
Written an X for data that is irrelevant for this specific formula (but necessary for me in the final analysis) and also started on column G as the ones before are also irrelevant.

The formula I have create looks like this:
=IF(Q2=$AF$2;$AF$2;(IF(ISNA(IF(G2=X2;$AF$2;INDEX(Q$2:Q$624765;MATCH(G2;X$2:X$624765;0))));(IF(ISNA(IF(G2=Y2;$AG$2;INDEX(Q$2:Q$624765;MATCH(G2;Y$2:Y$624765;0))));(IF(ISNA(IF(G2=Z2;$AH$2;INDEX(Q2:Q$624765;MATCH(G2;Z$2:Z$624765;0))));(IF(ISNA(IF(G2=AA2;$AI$2;INDEX(Q2:Q$624765;MATCH(G2;AA$2:AA$624765;0))));IF(Q2=$AJ$2;$AJ$2;INDEX(Q2:Q$624765;MATCH(G2;AB$2:AB$624765;0)));IF(G2=AA2;$AI$2;INDEX(Q2:Q$624765;MATCH(G2;AA$2:AA$624765;0)))));IF(G2=Z2;$AH$2;INDEX(Q2:Q$624765;MATCH(G2;Z$2:Z$624765;0)))));IF(G2=Y2;$AG$2;INDEX(Q$2:Q$624765;MATCH(G2;Y$2:Y$624765;0)))));IF(G2=X2;$AF$2;INDEX(Q$2:Q$624765;MATCH(G2;X$2:X$624765;0))))))

In short it checks if the User ID is visible in the columns X-AB in any of the 700K rows by first checking Dif1 then 2 and so on. The IF(ISNA) does so that when there result will be N/A as the user ID isn't visible in any rows in column X it will go to the next column and if the user ID is in that column it will then fill the cell with the name of that difficulty level.


The help I need then are one of 2 things.
1. First if you can help me simplify this is some way then please help because this formula takes about 1h to calculate for the whole sheet.
2. Second is that it sometimes returns REF error but I cant find why, only for about 1% of the total. But it does look like it does it only when the difficulty for the row is either 4 or 5. But doesn't always return REF in those situations.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Actually created an alternative formula by starting over and it may be shorter but it takes longer to calculate.
=IF(G2=X2;AF$2;(IF((COUNTIF(X$2:X$624765;G2))>0;AF$2;(IF((COUNTIF(Y$2:Y$624765;G2))>0;AG$2;(IF((COUNTIF(Z$2:Z$624765;G2))>0;AH$2;(IF((COUNTIF(AA$2:AA$624765;G2))>0;AI$2;(IF((COUNTIF(AB$2:AB$624765;G2))>0;AJ$2;)))))))))))

Suggestions?
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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