Usung Vllokup to look up Value

[howard]

I have used the following formula to look up the account number in col B and to give me the value of the account number which appears in workbook BR1TB. It gives me a value #N/A.

For eg Acc # 102535 should give me a value of -3470.42


I have attached some sample data. It would be appreciated if someone can assist me

Sales COS Account Numbers.xls
	A	B	C	D
2	OldAcc#	NewAcc#	Amount
3	3000	102535	#N/A
4	3001	0	#N/A
5	3002	102520	#N/A
6	3003	102560	#N/A
7	3004	102525	#N/A
8	3005	102565	#N/A
NV Sales

BR1TB.xls
	A	B	C	D	E
437	102535	SALES-RETAIL	-151623	-4545.8	-3470.42
438	102536	SALES-Q-FLEET	0	0	0
439	102537	FACTINCQ1	-3722.22	-222.222	-87.6543
440	102540	SALES-L1-RETAIL	-71298.5	-3267.04	-1657.01
NISSTB

 

[cgcamal]

Hi howard,

For me works. Seeing the formula in the image, maybe is missing the sheet name within the reference.

Excel Workbook
	A	B	C
2	Old Acc #	New Acc #	Amount
3	3000	102535	-3470.42
4	3001	0
5	3002	102520
6	3003	102560
7	3004	102525
8	3005	102565
...

Cell Formulas
Range	Formula
C3	=VLOOKUP(B3,[BR1TB.xls]SheetName!$A:$E,5,FALSE)

Hope this helps,

Regards

 

[Marcelo Branco]

Hi,

If the workbook is opened you can use something like

=VLOOKUP(B3,'[BR1TB.xls]NISSTB'!$A:$E,5,FALSE)

(as apparently there are no spaces neither in workbook-name nor in the sheet-name you could omit the apostrophes ' ' . I used them just in case.)


If the worbook is closed you need the path, for example

=VLOOKUP(B3,'C:\My Files\[BRITB.xls]NISSTB'!$A:$E,5,FALSE)


M.

 

[howard]

Thanks for the reply. I have used the formula =VLOOKUP(B3,[BRTB1.xls]Br1tb!$A$1:$E$65536,5,FALSE) and it gives me #N/A

See my sample files below. The Account number may be in numeric format, resulting in the error. it would be appreciated if you could assist me

Account Numbers.xls
	A	B	C	D
1	NVSales
2	OldAcc#	NewAcc#	Amount
3	3000	102535	Br1tb!$A$1:$E$65536,5,FALSE)]#N/A
4	3001	0	#N/A
5	3002	102520	#N/A
6	3003	102560	#N/A
7	3004	102525	#N/A
8	3005	102565	#N/A
9	3006	102505	#N/A
10	3007	102500	#N/A
11	3008	102550	#N/A
NV Sales

BR1TB.xls
	A	B	C	D	E
1	102522	FACINCENT-N1	-3904.19	-555.556	-99.1055
2	102525	SALES--RETAIL	-70097.2	-11282.6	-1808.44
3	102526	SALES-FLT	0	0	0
4	102527	FACINC	-5822.22	-1111.11	-154.074
5	102535	SALES-RETAIL	-151623	-4545.8	-3470.42
6	102536	SALES-Q-FLEET	0	0	0
7	102537	FACTINCQ1	-3722.22	-222.222	-87.6543
8	102540	SALES-L1-RETAIL	-1298.45	-3267.04	-101.456
Br1tb

 

[Aladin Akyurek]

Does...

=VLOOKUP(B3+0,[BRTB1.xls]Br1tb!$A:$E,5,0)

work as expected?

 

[howard]

Hi Aladin

The formula works perfectly. I have aapted this formula for another workbook to

=IF(ISNA(VLOOKUP(B3+0,NISSTB.xls!$A:$E,4,FALSE)),0,(VLOOKUP(B3+0,NISSTB.xls!$A:$E,4,FALSE)))

However if If have an account # with text in between the account number for eg 10G506,
10G516 etc, the result comes up as #value!

It would be appreciated if you could assist me in resolving this problem

Account Numbers.xls
	B	C	D	E
18	TB CONSOLIDATION'!$F:$G,2,FALSE)),0,VLOOKUP(TEXT(A18,0),'[M_NECPE 1011.xls]TB CONSOLIDATION'!$F:$G,2,FALSE))]10G506	#VALUE!
19	10G516	#VALUE!
20	10G502	#VALUE!
NV Sales

 

[Aladin Akyurek]

Hi Aladin

The formula works perfectly. I have aapted this formula for another workbook to

=IF(ISNA(VLOOKUP(B3+0,NISSTB.xls!$A:$E,4,FALSE)),0,(VLOOKUP(B3+0,NISSTB.xls!$A:$E,4,FALSE)))

However if If have an account # with text in between the account number for eg 10G506,
10G516 etc, the result comes up as #value!

It would be appreciated if you could assist me in resolving this problem

Account Numbers.xls
	B	C	D	E
18	TB CONSOLIDATION'!$F:$G,2,FALSE)),0,VLOOKUP(TEXT(A18,0),'[M_NECPE 1011.xls]TB CONSOLIDATION'!$F:$G,2,FALSE))]10G506	#VALUE!
19	10G516	#VALUE!
20	10G502	#VALUE!
NV Sales

Generic...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    VLOOKUP(IF(ISNUMBER(B3+0),B3+0,B3),NISSTB.xls!$A:$E,4,0)))

If you are on 2007 or later...

=IFERROR(VLOOKUP(IF(ISNUMBER(B3+0),B3+0,B3),NISSTB.xls!$A:$E,4,0),0)

 

[howard]

Hi Aladin

You're a star. The formula works perfectly. I have learnt something new about Vlookups today

Regards

Howard

 

[Aladin Akyurek]

Hi Aladin

You're a star. The formula works perfectly. I have learnt something new about Vlookups today

Regards

Howard

You are welcome. Thanks for providing feedback.