Index & Match / Vlookup Help

[Higenbo]

Hi,

I have two data sets. They are identical apart from one has an additional field that I want to Index & Match and return to my data set. The issue I am facing is I have instances that have multiple occurrences for my criteria.

Example

My Data

Col A Col B
1 ABC123
2 ABC123
3 ABC123


Data set I want to return Col E field back to my data set

Col D Col E
1 ABC123 XYZ123
2 ABC123 XYZ456
3 ABC123 XYZ789

So I need the Index and Match function to know there are three potential results that can be returned for my criteria 'ABC123' and return the first, then second and third.

My data set is live and keeps growing in volume with fields that are blank. I need to obtain these from a flat file and just investigate any new occurrences. I have no way of allocating a unique ID to my original data either within our system. Which is why I need a formula.

Help!!!

Cheers

 

[Special-K99]

in Sheet1!C1
=IFERROR(INDEX(Sheet2!$E$1:$E$3,AGGREGATE(15,6,ROW(Sheet2!$E$1:$E$3)/(Sheet2!$B1:$B3=B1),ROWS(A$1:A1))-(1-1),1),"")

This assumes Sheet1 is first data set with result appearing in column C
and second data set is on Sheet2

Amend formula as necessary

 

[Higenbo]

Thanks for the response. I can't get that to work. Looking back the above didn't come out very clear.

So for the below the formula in B1 would be

=IFERROR(INDEX($B$6:$B$8,AGGREGATE(15,6,ROW($B$6:$B$8)/($A6:$A8=A1),ROWS(A$1:A1))-(1-1),1),"")?

Thanks

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Data set 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC123[/TD]
[TD]XYZ123[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ABC123[/TD]
[TD]XYZ456[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ABC123[/TD]
[TD]XYZ789[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

My mistake

try

in B1
=IFERROR(INDEX($B$6:$B$8,AGGREGATE(15,6,ROW($A$6:$A$8)/($A$6:$A$8=A1),ROWS(A$1:A1))-(6-1),1),"")
and copy down to B3

The formula is a fairly standard response (though I've modified it a little) and mostly used when removing blanks from a list, though it can be any condition.

=IFERROR(INDEX(range,AGGREGATE(15,6,ROW(range)/(range to check and condition),ROWS(first cell reference with anchored row - first row))-(first row-1),column),"")