vlookup

luxbax12

New Member
Joined
Nov 3, 2005
Messages
46
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
ABCDE
1Acct #Current AmntNew AmntAcct #Sale Amnt
2000001119383010000040570.08
3100004000000010009180.00
400000129985000000010014180.00
500000177720100000010025119374.50
600000157930600000010025610.00
70000017974160000001005022361.80
80000014760100000001008587272.00
9000001651819000000101400517702.40
10000001767850000000101429689.00
110000011133470000001016083756.50
12000001025167000000101619931651.20
1300000128512400000010174001026.20
1400000162431600000010174890.00
1500000140520600000010179134904.30
1600000174159500000010181247309.17
1700000173421000000010182133654.85
1800000181672300000010183790.00
1900000141904200000010184160.00
2000000109938400000010190311456.20
210000017538190000001019072301.00
2200000141755900000010192527911.50
2300000151612400000010215824540.48
24000001268610000000102203636270.00
250000015712220000001024302797.40
2600000185150800000010297899386.30
27000001267904000000103046940171.95
280000018835570000001034151803.50
290000010163500000001034359136.50
300000017203330000001035793152.50
Sheet1
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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...
 
Upvote 0
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
ABCDEFGH
1SKUProductNamePrice2008PriceDeleteSKUItemNumberListPrice
21912011-DrwFileNoInserts275.00275DeleteSKUAOHT1826238.00
31912191-DrwCassetteFile275.00275DeleteSKUATC232177.00
41912371-DrwIndv.SlideFile275.00275DeleteSKUATC332203.00
51912631-DrwGroupSlideFile275.00275DeleteSKUATV56**574.00
61915121-DrwVideoTapeFile342.00342DeleteSKUATV56B678.00
71915131-DrwFileDataTape,Zip,1/2"Tape342.00342DeleteSKUATV56BC872.00
81915601-DrwCassetteFile342.00342DeleteSKUATV56C758.00
91915611-DrwCassetteFile342.00342DeleteSKUATV56KB4598.00
101917131-DrwFileDataTape,Zip,1/2"Tape383.00383DeleteSKUATW48CEUL1,010.00
111917141-DrwVideoTapeFile383.00383DeleteSKUATW48EUL702.00
121917801-DrwFloppyDiscFile416.00416DeleteSKUATW56550.00
131917851-DrwAudioDiscFile383.00383DeleteSKUATW56C907.00
141917991-DrwHighDensityCDStorage416.00416DeleteSKUAV26**358.00
151934153-DrwFile31/2"ComputerDisk639.00639DeleteSKUAV26B**435.00
161942014-DrwFileNoInserts565.00565DeleteSKUAV34**385.00
171942194-DrwCassetteFile565.00565DeleteSKUAV34B**462.00
181942374-DrwIndv.SlideFile565.00565DeleteSKUAV42**399.00
191942634-DrwGroupSlideFile565.00565DeleteSKUAV42B**478.00
201945V4-DrwVideoTapeFilew/RecessedBase1051.001051DeleteSKUAVJ42**340.00
211965V6-DrwVideoTapeFilew/RecessedBase1529.001529DeleteSKUAVJ42**353.00
2219BCCEquipmentStorageBasew/Casters391.00391DeleteSKUAVJ422KB4499.00
2319BCGEquipmentStorageBasew/Glides376.00376DeleteSKUAVJ42B**443.00
2419LAccessoryLockAssembly46.0046DeleteSKUAVJ42BC**616.00
2519RBRecessedBase92.0092DeleteSKUAVJ42BCKB**706.00
2619RBCRecessedBasew/Casters113.00113DeleteSKUAVJ42BCKBDL**758.00
2719TAccessoryTopSurface53.0053DeleteSKUAVJ42C**527.00
2835VT22-ShelfVideoTapeCabinet593.00593DeleteSKUAVJ42CKD508.00
2935VT33-ShelfVideoTapeCabinet673.00673DeleteSKUAVJ42KB**465.00
303917131-DrwDataTapeFile532.00532DeleteSKUAVJ42KB**477.00
Sheet1
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top