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

Lear_doctor,

Which is it ?

Because if you're talking about the latter, sooner or later its going to bite you in backside in a major way if you're trying to correctly guess / make assumptions as to where the correct data may be.

It sounds to me as you really need to fix whatever's feeding you the data or parse it better ?

All the batch numbers are in column 4. They are 'missing' ie need to be entered into column 1. Perhaps missing was a wrong choice of word. Required maybe?

The data comes from two sources, thats the problem. They need to be brought together at this point.



regards

The Doc

PM an email address - I have the solution (I think)

Resolved
 

Thanks to BOAC who came up with this brilliant solution

{=INDEX($F$3:$F$8,MATCH(B4&C4,$G$3:$G$8&$H$3:$H$8,0))}

best regards


The Doc