How do I extract a second data in a Lookup

G

Guest

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
 
G

Guest

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.
 
G

Guest

Max,

Many thanks. It is working perfectly! I could not have done it without your
help.

Darkwah.
Ghana
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I copy a table w/o certain entries? 2
How to extract a few data elements from the following data 1
How do I avoid hitting the limit on simultaneous open items? 1
How do I set up a standard form showing incentive calculations 2
how do I extract only certain data from a cell? 6
How do I lookup a value in a table which has many results? 2
In a Join, 0 doesn't join -0; why not? how to workaround? 2
Excel 2002: How to lookup the last number and text in a column ? 3

Top