Matching data in separate columns

[EBS]

I need to find a way to match up Inventory item codes which were imported into excel from 2 different sources. I need them sorted so the ones with no matches would leave a blank cell i.e.

Source one

Column A

Item No.

HD638
HD6516
HD812
HD838
HD858
HDS014
HDS018
HDS0316


Column B

No. Sold

1000
500
600
50
100
600
450


Source 2

Column C

Item No.
HD638
HD6516
HD838
HDS014
HDS018
HDS0316



Column D

Qty on Hand

5000
2000
1000
5000
2000
500



Column E

On Order

1000

[rylo]

Hi

Will source 1 always have all the codes from source 2, or is each source likely to have codes that may not be in the other source's data?


rylo

[EBS]

Hi

I have decided to use VLookup function and put each source on a different sheet and create a range name for each table and then the 3rd sheet for the total stock list.

The problem I have with this is that is shows #N/A for items not sold how can I make this show 0 if no items are sold.

The function I used for the column of items sold as one range table is =VLOOKUP(H4,tablesold,2,FALSE) and =VLOOKUP(H4,tableonhand,2,FALSE) for items on hand and then on order I kept in this same table but change the 2 to a 3 for the on hand amount.

[rylo]

Hi

The approach to take is

=if(isna(vlookup(....)),0,vlookup(....))

rylo

[EBS]

Thank you that is great is solves my question.