Index Match after sorting

[nuruchima]

I have one excel file that has two tabs that i want to work together. In Tab1 i want the priority list to be updated manually. Then on tab2 i want the priority list to be filled automatically from tab1.
The order numbers will be in different sequence on both tabs so i want the priority to transfer over properly for every order number. I entered a formula in tab 2 priority column that will index the priority list in tab 1, then match the order number of tab 1 to tab 2 with additional requirements(status and if status is blank), to pull the priority list from tab 1. Then i added an if statement that if the priority list in tab1 was blank, then it will stay blank on tab 2. Before that if statement it would show 0.

I got it working but the problem is that when i sort the priority list on tab 2, the code seems to not work or update. An example is order number 541. Normally it transfers correctly that its priority is 1. If i sort smallest to largest, it still shows the priority as 1. But if i sort from largest to smallest, it turns into #N/A. I have retyped the code and it still does not work. I have to put the sorting back to how it was first written for it to work again. No changes are made to the formula when sorting.

Is there any way to fix this? I don't have experience with BVA so i won't be able to understand it if you post a code of that instead.

Formula code is below.
=IF(INDEX('Tab1'!C$8:C$156,MATCH(B65,'Tab1'!B$8:B$381,0))="","",INDEX('Tab1'!C$8:C$186,MATCH(1,('Tab1'!B$8:B$675=B65)*((D$7:D$374=E$2)+(D$7:D$374=E$3)+(D$7:D$374="")),0)))

Thank You

 

[Eric W]

Welcome to the MrExcel forum!

In looking at your formula, I see a couple of issues:

=IF(INDEX('Tab1'!C$8:C$156,MATCH(B65,'Tab1'!B$8:B$381,0))="","",INDEX('Tab1'!C$8:C$186,MATCH(1,('Tab1'!B$8:B$675=B65)*((D$7:D$374=E$2)+(D$7:D$374=E$3)+(D$7:D$374="")),0)))

First, the lower boundaries of your ranges (red) are all over the place. They should match. Although it should still work if your key is found in the smallest of the ranges, it's confusing and could cause issues later.

Second, the upper boundaries of your ranges (green) do not match. They should all start at the same place, otherwise you'll get unpredictable results, which seems to be what you're seeing.

Give that a try.


Also, you might consider downloading and using the xl2bb add-in tool. It's easy to download, install and use. See the link in my signature or the reply box. It's much easier for people here to test your situation. You'll likely to get more/faster responses if you use it. I had to manually recreate your sheets, which a lot of people won't do. Good luck!