Compare cells in two different sheets and copy adjacent cell to specific cell

[ssbhatban]

Dear friends
I need a help in solving below problem

I have some 6000 alphanumeric data in column B of sheet 1, I have same or some of the same alpha numeric data of sheet 1 in column B in sheet 2 also. In column C & column D of sheet 2 i have three digit numbers
Now i want to compare column B of sheet 1 with column B of sheet 2, find the matching no & copy the adjacent cell value of column C & D in sheet 2 to matching row of column in B of sheet 1 & place the values in column E column F
If this can be done thru either V or H look up or built in function it is good or else VBA code also OK
It is very urgent can any body help me

 

[JEC]

Like this maybe?

In column E

=VLOOKUP(B1,Sheet2!$B$1:$D$100,2,0)

In column F

=VLOOKUP(B1,Sheet2!$B$1:$D$3,3,0)

 

[ssbhatban]

Thanks, looks fine, but my data in both index column will not be sorted, will it work for it

 

[JEC]

Yes, should not be a problem

 

[Snakehips]

Maybe like.....

Book1
	B	C	D	E
1	Lookup		FindC	FindD
2	Text12		C1	D11
3	Text13		C2	D12
4	Text14		C16	D26
5	Text25		C4	D14
6	Text16		C5	D15
7	Text17		C6	D16
8	Text18		s	t
9	Text19		C8	D18
10	Text26		C19	D29
11	Text21		C10	D20
12	Text22		C11	D21
13	Text23		C12	D22
14	Text24		C13	D23
15	Text15		C14	D24
16	Text20		C15	D25
17	Text27		C3	D13
18	Text28		C17	D27
19	Text29		C18	D28
20	xxxxxz		C9	D19
21
Sheet1

Cell Formulas
Range	Formula
D2:E21	D2	=IFERROR(VLOOKUP($B2,Sheet2!$B$1:$D$10000,COLUMNS($C:D),FALSE),"")

 

[ssbhatban]

Thanks for the solution, looks fine, will it also work if columns are not sorted

 

[Snakehips]

Thanks for the solution, looks fine, will it also work if columns are not sorted

Yes it should be fine.

Book1
	B	C	D
1	Stuff
2	Text21	C10	D20
3	Text13	C2	D12
4
5	Text15	C14	D24
6	Text16	C5	D15
7	Text17	C6	D16
8	xxxxxz	C9	D19
9	Text19	C8	D18
10	Text20	C15	D25
11	Text12	C1	C11
12	Text22	C11	D21
13	Text23	C12	D22
14	Text24	C13	D23
15	Abc	C4	D14
16	Text26	C19	D29
17	Text27	C3	D13
18	Text28	C17	D27
19	Text29	C18	D28
20	Text14	C16	D26
21	Text18	s	t
22
23
Sheet2

Why don't you test it using a sample or backed up data?

 

[ssbhatban]

Ok will try