Search for duplicates in 2 columns, return associated data

[Mr_Nivek]

Hello All,

The best way I can think to describe this is through an example:

In the example below I'd like to search for instances of entries from the first colum (Item Needed) in the second column (Item Ordered). If a match is found I'd like to have the "Item Ordered" and associated "Quantity" and "Unit Price ($)" values returned in the 3 columns following the table.

I appreciate any help I can get. Thank you.

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Item Needed[/TD]
[TD]Item Ordered[/TD]
[TD]Quantity[/TD]
[TD]Unit Price ($)[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]0.99[/TD]
[/TR]
[TR]
[TD]Peach[/TD]
[TD]Banana[/TD]
[TD]4[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]Pear[/TD]
[TD]Mango[/TD]
[TD]1[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pear[/TD]
[TD]2[/TD]
[TD]1.25[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Strawberry[/TD]
[TD]3[/TD]
[TD]0.50[/TD]
[/TR]
</TBODY>[/TABLE]

 

[NeonRedSharpie]

Assuming your data table begins in A1:

Column E

=IF(MATCH($A2,$B:$B)>0,$A2)

Column F

=IF(MATCH($A2,$B:$B)>0,INDEX($C:$C,MATCH($A2,$B:$B)))

Column G

=IF(MATCH($A2,$B:$B)>0,INDEX($D:$D,MATCH($A2,$B:$B)))