Search for a string and return specific value from multiple values in a column

Dear Gurus, I have multiple duplicate entries in the First Column and I have Unique Values in the Third Column. I'm looking for searching for a particular string in the Second column with reference to the unique values in the Third Column and Return the number part in the required Forth Column.

Attached the sheet with values.

Please help

Shiva

Try this in E2:

=IFERROR(LOOKUP(2,1/($A$2:$A$36=D2)/(LEFT($B$2:$B$36,2)="RS"),--RIGHT($B$2:$B$36,LEN($B$2:$B$36)-4)),"")

That's fantastic my dear friend. Thanks a million. Added Repute.

-Shiva

Hi Phuocam, this formula was working in my previous workbook, but doesn't seem to work (Not returning the RIGHT(),LEN()-4) as expected. I have attached the sheet.

I have applied =IF(F2=0,IFERROR(LOOKUP(2,1/($A$2:$A$17=E2)/(LEFT($D$2:$D$17,2)="VN"),--RIGHT($D$2:$D$17,LEN($D$2:$D$7)-4)),""),"")

in H2 Cell

AND

=IF(F2=1,IFERROR(LOOKUP(2,1/($A$2:$A$17=E2)/(LEFT($D$2:$D$17,2)="PI"),--RIGHT($D$2:$D$17,LEN($D$2:$D$7)-4)),""),"")

in I2 Cell

But not returning the values. Please help.

Thanks in advance

Shiva

Hi, could someone please help on this?

Thanks in advance
Shiva

Hi, I observed that the values "VN - PL3006" or "PI- IP5487" for example are alphanumeric is the reason why it is not returning the " LEN()-4 " values. When I'm putting a number for example "VN - 883006 and "PI - 125487" the formula is returning correctly.

Could someone help to resolve this?

Change =IF(F2=0,IFERROR(LOOKUP(2,1/($A$2:$A$17=E2)/(LEFT($D$2:$D$17,2)="VN"),--RIGHT($D$2:$D$17,LEN($D$2:$D$7)-4)),""),"")

to =IF(F2=0,IFERROR(LOOKUP(2,1/($A$2:$A$17=E2)/(LEFT($D$2:$D$17,2)="VN"),RIGHT($D$2:$D$17,LEN($D$2:$D$17)-4)),""),"")

Thanks ton buddy. It worked. Unable to add repute.