Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract a second data in a Lookup
I have a data that contains employee numbers and amount earned as bonus. Some
of the employees earn bonuses from one or two schemes. When I use VLOOKUP to extract data, the expression only picks the first amount that is found in the database. How can I go about it so that the second number of the same employee will pick the second amount? Extract from database Emp# Amt MC5604 $20 MC5604 $50 Current result Emp# Amt MC5604 $20 MC5604 $20 Expected result Emp# Amt MC5604 $20 MC5604 $50 Thank you Darkwah |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract a second data in a Lookup
Assume database in Sheet1, cols A and B, data from row2 down
Put in C2: =COUNTIF(A$2:A2,A2) Copy down Then in Sheet2, With the Emp#s running in row2 down place in B2, then array-enter the formula, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER: =INDEX(Sheet1!$B$2:$B$100,MATCH(A2&COUNTIF(A$2:A2, A2),Sheet1!$A$2:$A$100&Sheet1!$C$2:$C$100,0)) Copy B2 down to return required results. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Darkwah" wrote: I have a data that contains employee numbers and amount earned as bonus. Some of the employees earn bonuses from one or two schemes. When I use VLOOKUP to extract data, the expression only picks the first amount that is found in the database. How can I go about it so that the second number of the same employee will pick the second amount? Extract from database Emp# Amt MC5604 $20 MC5604 $50 Current result Emp# Amt MC5604 $20 MC5604 $20 Expected result Emp# Amt MC5604 $20 MC5604 $50 Thank you Darkwah |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract a second data in a Lookup
Max,
Many thanks. It is working perfectly! I could not have done it without your help. Darkwah. Ghana "Max" wrote: Assume database in Sheet1, cols A and B, data from row2 down Put in C2: =COUNTIF(A$2:A2,A2) Copy down Then in Sheet2, With the Emp#s running in row2 down place in B2, then array-enter the formula, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER: =INDEX(Sheet1!$B$2:$B$100,MATCH(A2&COUNTIF(A$2:A2, A2),Sheet1!$A$2:$A$100&Sheet1!$C$2:$C$100,0)) Copy B2 down to return required results. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Darkwah" wrote: I have a data that contains employee numbers and amount earned as bonus. Some of the employees earn bonuses from one or two schemes. When I use VLOOKUP to extract data, the expression only picks the first amount that is found in the database. How can I go about it so that the second number of the same employee will pick the second amount? Extract from database Emp# Amt MC5604 $20 MC5604 $50 Current result Emp# Amt MC5604 $20 MC5604 $20 Expected result Emp# Amt MC5604 $20 MC5604 $50 Thank you Darkwah |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract a second data in a Lookup
Pleased to hear that !
You're welcome. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Darkwah" wrote in message ... Max, Many thanks. It is working perfectly! I could not have done it without your help. Darkwah. Ghana |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO EXTRACT (or lookup) DATA FROM A PIVOT TABLE | New Users to Excel | |||
data extract | Excel Worksheet Functions | |||
AGAIN... I need another Lookup Function to extract some data | Excel Worksheet Functions | |||
How to extract the data | Excel Worksheet Functions | |||
Extract Data | Excel Discussion (Misc queries) |