In the above (obviously contrived) example, a company changed accounting systems. Any open projects at the time were converted to the new system, but were assigned new project numbers and names. Now the company needs to sort the reports by project number, but they want those jobs that have two different numbers to remain together.
To accomplish this, we need a new column (that we will be sorting on) and a lookup table showing both the old and new project numbers.
The new Sort Project column uses this formula:
That says: if you can’t find this project in column E, give me column A, otherwise give me column F. Now I can sort on column C and get Project One and New Project One sorted together.