Use this formula in sheet A

=INDEX(SheetB!$B$1:$B$10,SMALL(IF(SheetB!$A$1:$A$1 0=sheetA!$A
$2,ROW(SheetB!$A$1:$A$10)),ROW(1:1)),1)

use ctrl + shift + enter this is an array formula


On Sep 15, 12:14*pm, Andri wrote:
Dear Muddan,

thank you for the response. but that Vlookup formula only list one of SN
(the first occurance).

But i would like to list down all the SN if the Product Name is match.
SN = TEXT data.

Thank you and looking forward further guidance.

respectfully,
andry



"muddan madhu" wrote:
Try

In sheet A put this formula in B2 *=VLOOKUP(A2,Sheet2!$A$1:$B$20,2,0)
and drag it.

On Sep 15, 10:34 am, Andri wrote:
Dear All,

Please help to solve it by FORMULA.

I have a database e.g 20 Rows, the fields Product Name, SN, etc (Sheet A)

I would like to concatenate the SN, if match with the product Name (Sheet B).
For Example : if Col A, Product Name = Computer, Col B, i want to be text
the SN like this (A1234, B1234, C1234) from the database in Sheet A.

thank you for your kind attention and help.

TIA and respectfully,
andri- Hide quoted text -

- Show quoted text -