Help - vlookup

[intheblack]

Hi.

Wondering if someone could help with this. Sheet one below is the reference sheet. the following Data sheet is information pasted in. I am trying to get the names in Col a on the data sheet to match with the names in sheet one. Then, when matched they should return the 2 letter code in col c in col m on the data sheet.

I have a vlookup up formula there (in col m) (=VLOOKUP(A3,'Fund reporting catergories'!A:C,3,1) but it's not returning the correct codes? Am i missing something?

AMP Capital Enhanced Yield Fund - Cl A(AMP0685AU)		FI
AMP Cap Global Prop Securit Fd -Class A(AMP0974AU)		RS
Future Directions International Share WS(AMP0683AU)		GL
AMP Capital International Bond Fd - WS(AMP0268AU)		FI
AMP Capital Listed Property Trusts Fd WS(AMP0269AU)		RS
AMP Capital WS Global Equity - Value(NML0348AU)		GL
BlackRock Indexed Australian Bond Fund(BGL0105AU)		FI
BlackRock Indexed Australian Equity Fund(BGL0034AU)		AL
BlackRock Scientific Hdg Int'l Eqty Fd(BGL0109AU)		GL
BlackRock Indexed International Eqty Fd(BGL0106AU)		GL
BlackRock Scientific Australian Eqty Fd(BAR0814AU)		AL
Hyperion Small Growth Companies Fund(BNT0101AU)		AS
BT WS Balanced Returns Fund(BTA0806AU)		AL
BT WS Asian Share Fund(BTA0054AU)		GL
BT WS Australian Share Fund(BTA0055AU)		AL
Colonial First State WS Global Resources(FSF0038AU)		GL
Colonial First State WS Income Fund(FSF0139AU)		FI
Challenger Guaranteed Income Fund(HOW0087AU)		FI
Cash Holding Account		C
Aberdeen Multi-Asset Income Fund(CRS0001AU)		FI
Bentham Wholesale Global Income Fund(CSA0038AU)		FI

<tbody>
</tbody>

---

Cash Holding Account		100	0	0	0	0	0	0	28,863.89	3
Managed Investments
AMP Capital Enhanced Yield Fund - Cl A (AMP0685AU) ^	-	2	98	0	0	0	0	0	5,093.28	0		FI
Bentham Wholesale Global Income Fund (CSA0038AU)	8.2	12	15	75	0	0	0	-2	90,516.87	8		FI
BlackRock Indexed Australian Equity Fund (BGL0034AU)	7.2	0	0	0	100	0	0	0	82,754.44	7		FI
BlackRock Indexed International Eqty Fd (BGL0106AU)	7.2	0	0	0	0	100	0	0	82,899.23	7		FI
Colonial First State WS Income Fund (FSF0139AU) ^	-	4	96	0	0	0	0	0	227.92	0		FI
Dimensional Australian Value Trust (DFA0101AU)	5.4	0	0	0	100	0	0	0	61,996.55	5		FI
Dimensional Five-year Div Fixed Int Tr (DFA0108AU)	11	4	0	96	0	0	0	0	########	11		FI
Dimensional Global Small Company Trust (DFA0106AU)	5.4	1	0	0	0	99	0	0	63,229.78	6		FI
Dimensional Global Value Trust (DFA0102AU)	5.4	0	0	0	0	100	0	0	62,116.26	5		FI
Hyperion Small Growth Companies Fund (BNT0101AU)	2.7	5	0	0	95	0	0	0	32,023.29	3		C
Ironbark Karara Aus Small Companies (PAT0002AU)	2.7	6	0	0	94	0	0	0	32,448.48	3		C
Lazard Emerging Markets Eqty - I Class (LAZ0003AU)	4.5	2	0	0	0	98	0	0	46,770.38	4		C
Money Market Investment Account	13.8	100	0	0	0	0	0	0	########	11		EM
PIMCO EQT Wholesale Div Fixed Interest (ETL0016AU)	11	0	51	49	0	0	0	0	########	11		RS
UBS Clarion Global Property Securities (HML0016AU)	2.25	2	0	0	0	0	98	0	26,577.41	2		GL
Vanguard Aust Fixed Interest Index Fund (VAN0001AU)	11	0	100	0	0	0	0	0	########	11		FI
Vanguard Property Securities Index Fund (VAN0004AU)	2.25	0	0	0	0	0	100	0	26,227.23	2		AL
												#N/A

<tbody>
</tbody>

Thanks

 

[ukmikeb]

Hi

Your lookup table needs to be in alphabetical order.

hth

 

[liveinhope]

change your vlookup to =VLOOKUP(A3,'Fund reporting catergories'!A:C,3,0)

this wont fix the problem but it will show you that some/most of the vlaues you are looking up dont exist in column A on the 'fund reporting' sheet
your formula VLOOKUP(A3,'Fund reporting catergories'!A:C,3,1) returns the closest match if no exact match is found (not what you want)

then you need to fix up the names so they are the same in both sheets

example AMP Capital Enhanced Yield Fund - Cl A(AMP0685AU) on one sheet and AMP Capital Enhanced Yield Fund - Cl A (AMP0685AU) ^ on the other close but not a match </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[intheblack]

That's the issue - I didn't want to have to fix up the names. What about the bracketed code? as it's always exact on both sheets- Could that be used?

 

[liveinhope]

You wont have to do the fix up manually So far I have found 3 differences between the 2 sheets eg AMP Capital Enhanced Yield Fund - Cl A(AMP0685AU) on one sheet and AMP Capital Enhanced Yield Fund - Cl A (AMP0685AU) ^ on the other

- ^ on the 2nd sheet . does not appear on the 1st
- space before the bracket on 2nd sheet no space on the first
- space after bracket on 2nd shhet . no space on the first (not so obvious but its there)

I put 3 new columns in my sheet ; on each column I gradaully remove the differences . the re-did the VLOOKUP using the value in col Q to lookup the other sheet - and this time it works

col o =SUBSTITUTE(A3,"^","")
col P =SUBSTITUTE(O3," (","(")
col Q =SUBSTITUTE(P3,") ",")")

vlookup =VLOOKUP(Q3,Sheet8!A:C,3,0)

Holding Account		100	0	0	0	0	0	0	28,863.89	3			col o	col p	col q
Managed Investments
AMP Capital Enhanced Yield Fund - Cl A (AMP0685AU) ^	-	2	98	0	0	0	0	0	5,093.28	0		FI	AMP Capital Enhanced Yield Fund - Cl A (AMP0685AU)	AMP Capital Enhanced Yield Fund - Cl A(AMP0685AU)	AMP Capital Enhanced Yield Fund - Cl A(AMP0685AU)
Bentham Wholesale Global Income Fund (CSA0038AU)	8.2	12	15	75	0	0	0	-2	90,516.87	8		FI	Bentham Wholesale Global Income Fund (CSA0038AU)	Bentham Wholesale Global Income Fund(CSA0038AU)	Bentham Wholesale Global Income Fund(CSA0038AU)
BlackRock Indexed Australian Equity Fund (BGL0034AU)	7.2	0	0	0	100	0	0	0	82,754.44	7		AL	BlackRock Indexed Australian Equity Fund (BGL0034AU)	BlackRock Indexed Australian Equity Fund(BGL0034AU)	BlackRock Indexed Australian Equity Fund(BGL0034AU)
BlackRock Indexed International Eqty Fd (BGL0106AU)	7.2	0	0	0	0	100	0	0	82,899.23	7		GL	BlackRock Indexed International Eqty Fd (BGL0106AU)	BlackRock Indexed International Eqty Fd(BGL0106AU)	BlackRock Indexed International Eqty Fd(BGL0106AU)
Colonial First State WS Income Fund (FSF0139AU) ^	-	4	96	0	0	0	0	0	227.92	0		FI	Colonial First State WS Income Fund (FSF0139AU)	Colonial First State WS Income Fund(FSF0139AU)	Colonial First State WS Income Fund(FSF0139AU)
Dimensional Australian Value Trust (DFA0101AU)	5.4	0	0	0	100	0	0	0	61,996.55	5		#N/A	Dimensional Australian Value Trust (DFA0101AU)	Dimensional Australian Value Trust(DFA0101AU)	Dimensional Australian Value Trust(DFA0101AU)
Dimensional Five-year Div Fixed Int Tr (DFA0108AU)	11	4	0	96	0	0	0	0	########	11		#N/A	Dimensional Five-year Div Fixed Int Tr (DFA0108AU)	Dimensional Five-year Div Fixed Int Tr(DFA0108AU)	Dimensional Five-year Div Fixed Int Tr(DFA0108AU)
Dimensional Global Small Company Trust (DFA0106AU)	5.4	1	0	0	0	99	0	0	63,229.78	6		#N/A	Dimensional Global Small Company Trust (DFA0106AU)	Dimensional Global Small Company Trust(DFA0106AU)	Dimensional Global Small Company Trust(DFA0106AU)
Dimensional Global Value Trust (DFA0102AU)	5.4	0	0	0	0	100	0	0	62,116.26	5		#N/A	Dimensional Global Value Trust (DFA0102AU)	Dimensional Global Value Trust(DFA0102AU)	Dimensional Global Value Trust(DFA0102AU)
Hyperion Small Growth Companies Fund (BNT0101AU)	2.7	5	0	0	95	0	0	0	32,023.29	3		AS	Hyperion Small Growth Companies Fund (BNT0101AU)	Hyperion Small Growth Companies Fund(BNT0101AU)	Hyperion Small Growth Companies Fund(BNT0101AU)
Ironbark Karara Aus Small Companies (PAT0002AU)	2.7	6	0	0	94	0	0	0	32,448.48	3		#N/A	Ironbark Karara Aus Small Companies (PAT0002AU)	Ironbark Karara Aus Small Companies(PAT0002AU)	Ironbark Karara Aus Small Companies(PAT0002AU)
Lazard Emerging Markets Eqty - I Class (LAZ0003AU)	4.5	2	0	0	0	98	0	0	46,770.38	4		#N/A	Lazard Emerging Markets Eqty - I Class (LAZ0003AU)	Lazard Emerging Markets Eqty - I Class(LAZ0003AU)	Lazard Emerging Markets Eqty - I Class(LAZ0003AU)
Money Market Investment Account	13.8	100	0	0	0	0	0	0	########	11		#N/A	Money Market Investment Account	Money Market Investment Account	Money Market Investment Account
PIMCO EQT Wholesale Div Fixed Interest (ETL0016AU)	11	0	51	49	0	0	0	0	########	11		#N/A	PIMCO EQT Wholesale Div Fixed Interest (ETL0016AU)	PIMCO EQT Wholesale Div Fixed Interest(ETL0016AU)	PIMCO EQT Wholesale Div Fixed Interest(ETL0016AU)
UBS Clarion Global Property Securities (HML0016AU)	2.25	2	0	0	0	0	98	0	26,577.41	2		#N/A	UBS Clarion Global Property Securities (HML0016AU)	UBS Clarion Global Property Securities(HML0016AU)	UBS Clarion Global Property Securities(HML0016AU)
Vanguard Aust Fixed Interest Index Fund (VAN0001AU)	11	0	100	0	0	0	0	0	########	11		#N/A	Vanguard Aust Fixed Interest Index Fund (VAN0001AU)	Vanguard Aust Fixed Interest Index Fund(VAN0001AU)	Vanguard Aust Fixed Interest Index Fund(VAN0001AU)
Vanguard Property Securities Index Fund (VAN0004AU)	2.25	0	0	0	0	0	100	0	26,227.23	2		#N/A	Vanguard Property Securities Index Fund (VAN0004AU)	Vanguard Property Securities Index Fund(VAN0004AU)	Vanguard Property Securities Index Fund(VAN0004AU)

<COLGROUP><COL style="WIDTH: 303pt; mso-width-source: userset; mso-width-alt: 14774" width=404><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" span=2 width=31><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" span=3 width=31><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width=20><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5522" width=151><COL style="WIDTH: 17pt; mso-width-source: userset; mso-width-alt: 841" width=23><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 297pt; mso-width-source: userset; mso-width-alt: 14482" width=396><COL style="WIDTH: 278pt; mso-width-source: userset; mso-width-alt: 13531" width=370><COL style="WIDTH: 269pt; mso-width-source: userset; mso-width-alt: 13092" width=358><TBODY>
</TBODY>

 

[intheblack]

Thanks Liveinhope - I changed as per your advice but but still coming up with #N/A in Col R. this is the formula in Col R =VLOOKUP(Q2,'Fund reporting catergories'!A:C,3,0)

If I change the vlookup to 1 - it will display FI for all funds which is incorrect as well?

AMP0685AU	-	2	98	0	0	0	0	0	5,093.28	0				AMP0685AU	AMP0685AU	AMP0685AU	#N/A
Bentham Wholesale Global Income Fund (CSA0038AU)	8.2	12	15	75	0	0	0	-2	90,516.87	8				Bentham Wholesale Global Income Fund (CSA0038AU)	Bentham Wholesale Global Income Fund(CSA0038AU)	Bentham Wholesale Global Income Fund(CSA0038AU)	#N/A
BlackRock Indexed Australian Equity Fund (BGL0034AU)	7.2	0	0	0	100	0	0	0	82,754.44	7				BlackRock Indexed Australian Equity Fund (BGL0034AU)	BlackRock Indexed Australian Equity Fund(BGL0034AU)	BlackRock Indexed Australian Equity Fund(BGL0034AU)	#N/A
BGL0106AU	7.2	0	0	0	0	100	0	0	82,899.23	7				BGL0106AU	BGL0106AU	BGL0106AU	#N/A

<colgroup><col span="14"><col><col><col><col></colgroup><tbody>
</tbody>

 

[liveinhope]

you dont want to change the last parameter to 1.

if the last parameter is 0 and you dont get an exact match the result in #N/A

change last parameter to 1 and it "matches" on the closest match which is likley to be not a match at all . this cloest match otion is really for things like

"how much interest will I get" - in this example any amt for 0 to $9,999.99 get 2% , $10,000 to $19,999 5% , $20,000 to $99,999 6% and above that 8%

0 2%
10,000 5%
20,000 6%
100,00 8%

have a look at your N/A'S . Is there a close-matching entry to the other table ? What is different ? you may need some more "substitutes"

 

[intheblack]

I'll go through them again, thanks.