Combining Sheets and Cross Referencing Data

[egates]

Good Evening - I am meeting myself coming backwards and wondered if someone could help me! I have two large spreadsheets holding client information. One has standard contact details on and one has relevant industry related details on and I want to combine them onto one.
Each client has an identifier number so I am guessing I can somehow link them using that but I am struggling to see how!
I would be very grateful if someone could point me in the right direction please and thank you

 

[CA_Punit]

Book1
	B	C	D	E	F	G	H	I	J
2
3	Sheet 1						Sheet 2
4
5	Client	Address	Contact	Details			Client	Detail
6	a	1	11	#N/A			d	Order Id 200
7	b	1	12	Order Id 500			b	Order Id 500
8	c	1	13	#N/A
9	d	2	21	Order Id 200
10	e	2	22	#N/A
11
12
Sheet1

Cell Formulas
Range	Formula
E6:E10	E6	=VLOOKUP(B6,H:I,2,0)

You can use look-up to find relevant details from Sheet 2-Relating to particular Client
If client information is not available in Sheet 2 then it wont show up and represent by #NA
However there are some limitation also
1. If there are duplicate then it will always show the first one

 

[NSCJPN]

Use Power Query to establish relationship and merge