Vlookup multiple results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear All,

I have 2 sheets wherein there is one common field. I have to consolidate the information from both the sheets. I can use vlookup function for picking of data from one sheet to another. But the problem is vlookup returns just the first data whenever the criteria matches. The common field is not a unique entry. What i desire is that the first time it returns the first matched data, second time it returns the second matched data if the criteria is matched again and so on. I tried using array formulae in vlookup but it does not work.

Kindly provide me with your valuable suggestions.

Regards
 
I have a recommendation, however, just one question: Do you know before hand the number of duplications there are for each item you are trying to look up? If you knew here is my suggestion:

From the source table sort it by the reference index (Say, Column A), insert or find a blank column to the table (Say, Column K), and assuming your first row is your title row, enter the following the formula in K2: "=if(A2=A1,K1+1,1)". This will give you an unique identifier for each duplicated item on Column A. Now add another column, say Column L to concatenate Column A and Column K. You can now use Column K as your vlookup reference index. Obvious, your designation reference index should be match the source reference index.
 
Back
Top