Getting data using vlookup or similar

[jdhfch]

Hi,

I am trying to extract data from a worksheet into another one using an account number as the reference. On the destination workbook, the account number will only appear once (as per column AC), the source data could have the account number in multiple times and some of the fields on the source file could be blank (or zero value). If I use Vlookup, it is finding only the first instance, and not the one with the data I need in. Below is an example of the dataset and what I would like to see. Can anyone help with this please?

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I
1	Acc No	Status1	Status2	Status3		Acc No	Status1	Status2	Status3
2	101	A				101	A	B	C
3	101		B			102		B
4	101			C		103	A		C
5	102		B
6	103	A
7	103			C
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(COUNTIFS($A$2:$A$7,$F2,B$2:B$7,"A")=1,"A","")
H2:H4	H2	=IF(COUNTIFS($A$2:$A$7,$F2,C$2:C$7,"B")=1,"B","")
I2:I4	I2	=IF(COUNTIFS($A$2:$A$7,$F2,D$2:D$7,"C")=1,"C","")

 

[jdhfch]

Per

try this

Book1
	A	B	C	D	E	F	G	H	I
1	Acc No	Status1	Status2	Status3		Acc No	Status1	Status2	Status3
2	101	A				101	A	B	C
3	101		B			102		B
4	101			C		103	A		C
5	102		B
6	103	A
7	103			C
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=IF(COUNTIFS($A$2:$A$7,$F2,B$2:B$7,"A")=1,"A","")
H2:H4	H2	=IF(COUNTIFS($A$2:$A$7,$F2,C$2:C$7,"B")=1,"B","")
I2:I4	I2	=IF(COUNTIFS($A$2:$A$7,$F2,D$2:D$7,"C")=1,"C","")

Perfect.. thank you

 

[AlanY]

Per

Perfect.. thank you

you're welcome