Exel lookup help
Thread Starter
Join Date: Oct 2002
Location: Dubai, UAE
Posts: 92
Likes: 0
Received 0 Likes
on
0 Posts
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
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.
Join Date: Aug 2002
Location: Earth
Posts: 3,663
Likes: 0
Received 0 Likes
on
0 Posts
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 ?
if the batch number is missing/not in the right place
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 ?
Thread Starter
Join Date: Oct 2002
Location: Dubai, UAE
Posts: 92
Likes: 0
Received 0 Likes
on
0 Posts
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
The data comes from two sources, thats the problem. They need to be brought together at this point.
regards
The Doc
Thread Starter
Join Date: Oct 2002
Location: Dubai, UAE
Posts: 92
Likes: 0
Received 0 Likes
on
0 Posts
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
{=INDEX($F$3:$F$8,MATCH(B4&C4,$G$3:$G$8&$H$3:$H$8,0))}
best regards
The Doc
Last edited by Lear_doctor; 14th Sep 2013 at 06:41.