Match values of 2 columns

[Pampos]

Hello.
I have column A with some text value from A1 to A15.
I have column B with some text value from B1 to B1000.
I have column C with some text value from C1 to C1000.
I have column D with some text value from D1 to D1000.

Every text of column A can be found on column B. So, 15 cells of column B have the same text as cells of column B. For example, A1 is the same with (and only) B23, A2 with B345 etc.

I want a way to match cells of column A with the cells of column B that have the same text. (Of course, I don't want to change column C and D. For example the text of B2 must remain with the text of C2 and D2, if I match it with A14).

Is that possible?

Thanks.
Xaris

 

[rahulshewale1]

hii,

please upload your workbook .so we might be help .
please provide desire output result also


Regard
Rahul shewale

 

[Pampos]

Here is an example. Small one.

 

[rahulshewale1]

Hii,

pfa attached result but i didn't understand logic behind in output sheet B16:D20

=INDEX(OFFSET($A$1,MATCH($K1,$B$1:$B$20,0)-1,1,1,3),,COLUMNS($A:A))



Regard
Rahul shewale

 

[rahulshewale1]

hii,

Please find the attached revised formula.with helper column

Regard
Rahul shewale

 

[bosco_yip]

A formula solution without helper column way.

1] A1 copied down:

=IF(data!A1>0,data!A1,"")

2] B1 copied down:

=IFERROR(INDEX(data!$B$1:$B$20,MATCH(A1,data!$B$1:$B$20,0)),IFERROR(INDEX(data!B$1:B$20,AGGREGATE(15,6,ROW(data!B$1:B$20)/ISNA(MATCH(data!B$1:B$20,A$1:A$20,0)),ROWS($1:1)-COUNTIF(A$1:A1,"><"))),""))

3] C1 copied down:

=IF($B1="","",VLOOKUP($B1,data!$B$1:$D$20,2,0))

4] D1 copied down:

=IF($B1="","",VLOOKUP($B1,data!$B$1:$D$20,3,0))

Regards
Bosco