vlookup

[luxbax12]

Hi All,
if acct # in column A matches an account # in column D then i would like for the value in column E to be placed in Column C.If the account number is not listed in column D
then the value in column B would be placed in Column C.
Thanks For any advice.

customer types_060608.xls
	A	B	C	D	E
1	Acct #	Current Amnt	New Amnt	Acct #	Sale Amnt
2	000001119383	0		10000040	570.08
3	1000040	0		000001000918	0.00
4	000001299850	0		000001001418	0.00
5	000001777201	0		000001002511	9374.50
6	000001579306	0		000001002561	0.00
7	000001797416	0		000001005022	361.80
8	000001476010	0		000001008587	272.00
9	000001651819	0		000001014005	17702.40
10	000001767850	0		000001014296	89.00
11	000001113347	0		000001016083	756.50
12	000001025167	0		000001016199	31651.20
13	000001285124	0		000001017400	1026.20
14	000001624316	0		000001017489	0.00
15	000001405206	0		000001017913	4904.30
16	000001741595	0		000001018124	7309.17
17	000001734210	0		000001018213	3654.85
18	000001816723	0		000001018379	0.00
19	000001419042	0		000001018416	0.00
20	000001099384	0		000001019031	1456.20
21	000001753819	0		000001019072	301.00
22	000001417559	0		000001019252	7911.50
23	000001516124	0		000001021582	4540.48
24	000001268610	0		000001022036	36270.00
25	000001571222	0		000001024302	797.40
26	000001851508	0		000001029789	9386.30
27	000001267904	0		000001030469	40171.95
28	000001883557	0		000001034151	803.50
29	000001016350	0		000001034359	136.50
30	000001720333	0		000001035793	152.50
Sheet1

 

[Aladin Akyurek]

In C2 enter and copy down:

=IF(ISNUMBER(MATCH(A2,D:D,0)),INDEX(E:E,MATCH(A2,D:D,0)),B2)

 

[luxbax12]

Aladin,
I tried that and it gave me all 0's.
for example acct # 1000040 in column A should match the acct # in Column
D there for dropping the value of 570.08.
I acually have 3000 accounts in column A.
Thanks for the help.

 

[Aladin Akyurek]

Aladin,
I tried that and it gave me all 0's.
for example acct # 1000040 in column A should match the acct # in Column
D there for dropping the value of 570.08.
I acually have 3000 accounts in column A.
Thanks for the help.

Columns A and D must be of the same data type: either both text or number...

 

[luxbax12]

Hi Aladin,
Here is a sample of a spreadsheet with a VLOOKUP formula that worked for a different project that VOGII helped me with..I made sure that column A and Column D were both number values, and still couldn't
get it to work on the current sheet.

thanks For you time and help.

LuxorFurnProductsforPricing (4).xls
	A	B	C	D	E	F	G	H
1	SKU	ProductName	Price	2008Price	DeleteSKU		ItemNumber	ListPrice
2	191201	1-DrwFileNoInserts	275.00	275	DeleteSKU		AOHT1826	238.00
3	191219	1-DrwCassetteFile	275.00	275	DeleteSKU		ATC232	177.00
4	191237	1-DrwIndv.SlideFile	275.00	275	DeleteSKU		ATC332	203.00
5	191263	1-DrwGroupSlideFile	275.00	275	DeleteSKU		ATV56**	574.00
6	191512	1-DrwVideoTapeFile	342.00	342	DeleteSKU		ATV56B	678.00
7	191513	1-DrwFileDataTape,Zip,1/2"Tape	342.00	342	DeleteSKU		ATV56BC	872.00
8	191560	1-DrwCassetteFile	342.00	342	DeleteSKU		ATV56C	758.00
9	191561	1-DrwCassetteFile	342.00	342	DeleteSKU		ATV56KB4	598.00
10	191713	1-DrwFileDataTape,Zip,1/2"Tape	383.00	383	DeleteSKU		ATW48CEUL	1,010.00
11	191714	1-DrwVideoTapeFile	383.00	383	DeleteSKU		ATW48EUL	702.00
12	191780	1-DrwFloppyDiscFile	416.00	416	DeleteSKU		ATW56	550.00
13	191785	1-DrwAudioDiscFile	383.00	383	DeleteSKU		ATW56C	907.00
14	191799	1-DrwHighDensityCDStorage	416.00	416	DeleteSKU		AV26**	358.00
15	193415	3-DrwFile31/2"ComputerDisk	639.00	639	DeleteSKU		AV26B**	435.00
16	194201	4-DrwFileNoInserts	565.00	565	DeleteSKU		AV34**	385.00
17	194219	4-DrwCassetteFile	565.00	565	DeleteSKU		AV34B**	462.00
18	194237	4-DrwIndv.SlideFile	565.00	565	DeleteSKU		AV42**	399.00
19	194263	4-DrwGroupSlideFile	565.00	565	DeleteSKU		AV42B**	478.00
20	1945V	4-DrwVideoTapeFilew/RecessedBase	1051.00	1051	DeleteSKU		AVJ42**	340.00
21	1965V	6-DrwVideoTapeFilew/RecessedBase	1529.00	1529	DeleteSKU		AVJ42**	353.00
22	19BCC	EquipmentStorageBasew/Casters	391.00	391	DeleteSKU		AVJ422KB4	499.00
23	19BCG	EquipmentStorageBasew/Glides	376.00	376	DeleteSKU		AVJ42B**	443.00
24	19L	AccessoryLockAssembly	46.00	46	DeleteSKU		AVJ42BC**	616.00
25	19RB	RecessedBase	92.00	92	DeleteSKU		AVJ42BCKB**	706.00
26	19RBC	RecessedBasew/Casters	113.00	113	DeleteSKU		AVJ42BCKBDL**	758.00
27	19T	AccessoryTopSurface	53.00	53	DeleteSKU		AVJ42C**	527.00
28	35VT2	2-ShelfVideoTapeCabinet	593.00	593	DeleteSKU		AVJ42CKD	508.00
29	35VT3	3-ShelfVideoTapeCabinet	673.00	673	DeleteSKU		AVJ42KB**	465.00
30	391713	1-DrwDataTapeFile	532.00	532	DeleteSKU		AVJ42KB**	477.00
Sheet1

 

[Aladin Akyurek]

Why don't we keep the discussion to your original sample? Don't be misled by the fact that I proposed a formula with Index/Match instead of Vlookup.

Would you enter the following in C2 in your original exhibit:

=ISNUMBER(A2)

and report back what this yields?

 

[luxbax12]

Aladin,
I pasted that into C2 and the results were false.

Thanks.

 

[Aladin Akyurek]

Aladin,
I pasted that into C2 and the results were false.

Thanks.

OK. Would you track down the cell in column D which is supposed to be equal to A2, apply ISNUMBER to that cell, and report back the result?

 

[luxbax12]

Aladin,
It came back a true value.
Column D has 488 account #s with a sales figure next to them.
Column A Has 3000 account numbers which 488 are in cloumn D.
The ones that are not listed in colun D should get a 0 $ figure in column C.

Thanks for your help.

 

[Aladin Akyurek]

Aladin,
It came back a true value.
Column D has 488 account #s with a sales figure next to them.
Column A Has 3000 account numbers which 488 are in cloumn D.
The ones that are not listed in colun D should get a 0 $ figure in column C.

Thanks for your help.

The results prove that column A is text and column D is numeric...

The formula I proposed should work when the columns we match of the same type. You should make sure that they are of the same type.

In the mean type, a modified version:

=IF(ISNUMBER(MATCH(A2+0,D:D,0)),INDEX(E:E,MATCH(A2+0,D:D,0)),B2)

might do the job.