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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Orange[/TD]
[TD]10Kilo[/TD]
[TD]sheet1[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Orange[/TD]
[TD]20Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Orange[/TD]
[TD]30Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Orange[/TD]
[TD]40Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Apple[/TD]
[TD]5Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Apple[/TD]
[TD]15Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Apple[/TD]
[TD]25Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Apple[/TD]
[TD]35Kilo[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Orange[/TD]
[TD]10Kilo[/TD]
[TD]sheet2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Orange[/TD]
[TD]20Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Orange[/TD]
[TD]30Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Orange[/TD]
[TD]40Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Apple[/TD]
[TD]5Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Apple[/TD]
[TD]15Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Apple[/TD]
[TD]25Kilo[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Apple[/TD]
[TD]35Kilo[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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