Comparing two columns and if there is a match then return values from a different column

Hi,

I have 3 columns.
Need to check whether col1 value exist in col2 and if it does, then pick up the values from col3 adjacent to col2 and populate the values in col 4 & col 5. For a value in col1, there can be multiple values in col 3(at max 2).

Capture.PNG

I have attached an image containing my input and output.


Please help.

Hi skywalkertinks- Paste this ARRAY FORMULA in D1 and then press CTRL+SHIFT+ENTER to confirm array status.
Then use the drag-handle (little green box, lower right corner) to fill across, and down.

= IFERROR(INDEX($C$1:$C$11,MATCH(0,COUNTIF($C1:C1,$C$1:$C$11)+($B$1:$B$11<>$A1),0)),"")
Untitled.png

NOTE: If the formula appears as TEXT after pasting, there's an unwanted leading space in front of the "=". Delete it, then press CTRL+SHIFT+ENTER again.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Happy to help!
Untitled.png

This part achieves uniqueness:
COUNTIF($C1:C1,$C$1:$C$11)

Note that the first C1 is absolute, while the second is relative, so when copied to right, it would read $C1:D1.
So this clause yields an array with a 1 for each element in column C that was already found, and a 0 for the rest.

In truth, I should have placed the formula starting in column E to avoid overlap with the data in Column C.
This would avoid some possible conflicts. The formula (in E1) would then read:
= IFERROR(INDEX($C$1:$C$11,MATCH(0,COUNTIF($D1:D1,$C$1:$C$11)+($B$1:$B$11<>$A1),0)),"")