# vlookup

#### luxbax12

##### New Member
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.
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

##### MrExcel MVP
In C2 enter and copy down:

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

#### luxbax12

##### New Member
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.

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
I pasted that into C2 and the results were false.

Thanks.

##### MrExcel MVP
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

##### New Member
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.

##### MrExcel MVP
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.

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.

Replies
1
Views
390
Replies
1
Views
236
Replies
0
Views
269
Replies
5
Views
119
Replies
0
Views
186

1,191,134
Messages
5,984,864
Members
439,921
Latest member
Neocold

### 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.

### Which adblocker are you using?

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

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