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

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

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

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