Lookup Multiple values from single value in a range

[mu10na]

Hello Experts,

Need your invaluable insight into the issue that I am facing below,

My data is as below,

Column A Column B
Orange 10Kilo
Orange 20Kilo
Orange 30Kilo
Orange 40Kilo
Apple 5Kilo
Apple 15Kilo
Apple 25Kilo
Apple 35Kilo

On another sheet I have the below columns. Please let me know how I can lookup column C to return values into Column D from Column A above in the same sequence.

Column C Column D
Orange
Orange
Orange
Orange
Apple
Apple
Apple
Apple

Any help would be greatly appreciated.

 

[C with no eyes]

INDEX works best with a unique value to search by. I presume there are other columns with your data? If so, is there a column that, when put together with oranges/apples, would make a completely unique reference? i.e. invoice ref, exact timestamp etc.

Helper columns are useful in this situation.

On Sheet1! make column C your unique reference. e.g. 365562oranges. This can be done by using CONCATENATE or & between the multiple values. Create the unique value on Sheet2! too and use INDEX and MATCH.

If do not have a column that can be put with oranges/apples then have (yet another) helper column to number the instances =COUNTIF($A$1:$A1,A1)
This can then be concatenated with column A to create the aforementioned unique reference.

I hope this makes sense. If you could provide a couple of rows of data for each sheet then I can be more specific.

 

[MARZIOTULLIO]

<tbody>
</tbody>

	A​	B​	C​
1​	Orange	10Kilo	sheet1
2​	Orange	20Kilo
3​	Orange	30Kilo
4​	Orange	40Kilo
5​	Apple	5Kilo
6​	Apple	15Kilo
7​	Apple	25Kilo
8​	Apple	35Kilo

<tbody>
</tbody>

	C​	D​	E​
1​	Orange	10Kilo	sheet2
2​	Orange	20Kilo
3​	Orange	30Kilo
4​	Orange	40Kilo
5​	Apple	5Kilo
6​	Apple	15Kilo
7​	Apple	25Kilo
8​	Apple	35Kilo

<tbody>
</tbody>

SHEET2

D1=IFERROR(INDEX(Sheet1!$B$1:$B$8,SMALL(IF(Sheet1!$A$1:$A$8=Sheet2!C1,ROW(Sheet1!$B$1:$B$8)-ROW(Sheet1!$B$1)+1),COUNTIF(Sheet2!$C$1:Sheet2!C1,Sheet2!C1))),"")

control+shift+enter copy down