Exel lookup help
Hello all - Wonder if you could help? - I have some data in excel, its in 6 columns as shown below. The problem if the batch number is missing/not in the right place. I need a formula or a plan that will essentially look at the invoice column and line item column (Columns 2 and 3 below) and find the corresponding batch number from Columns 4,5 and 6.
So for example in line two below, the missing number is 8888 as thats the batch number that corresponds with 1234 and 102 in columns 3 to 5.
Batch Invoice Line Item Batch Invoice Line item
Missing 1234 010 546 1234 010
Missing 1234 102 987 2525 012
Missing 5678 010 123 9000 034
Missing 5678 010 876 5678 010
Missing 1234 102 123 9000 034
Missing 999 777 8888 1234 102
Result
546 1234 010
8888 1234 102
876 5678 010
8888 1234 102
etc
regards
The Doc
Last edited by Lear_doctor; 13th Sep 2013 at 14:36.