Vlookup issue (formatting?)

[BHarrison]

Hi all,

Currently having an issue doing a vlookup. For what seems to be something very simple, it is not working as it should and I am not sure if you could help me out. I think it could be due to the fact that the files are different, but once again I am not sure. I am looking to get updated sales data into my salesforce records.

*Important Notes:
- I am currently using Excel for Mac
- I NEED to reference/lookup based on client ID#, as there can be multiple customers with the same Customer name

I currently export sales reports from my company records as a (.csv) file, I will export my clients via salesforce as an excel file (.xlsx). I then copy all my clients to one sheet, and do a vlookup to find the updated sales. This should be done in a simple vlookup, however I always get N/A as my result, despite the function being correct. I have tried formatting the Client ID# as numbers, general, custom, etc. but still to no avail. A simplified example below is provided.

Sales Report (.csv)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Client ID#[/TD]
[TD]Client Name[/TD]
[TD]Last 12 months sales[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Bob's bakery[/TD]
[TD]$120,000[/TD]
[/TR]
[TR]
[TD]1101[/TD]
[TD]Mike's Place[/TD]
[TD]$104,500[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Diane's Desserts[/TD]
[TD]$28,000[/TD]
[/TR]
[TR]
[TD]1021[/TD]
[TD]Bob's Bakery[/TD]
[TD]$27,000[/TD]
[/TR]
</tbody>[/TABLE]



Salesforce --> Updated Sales is what I am trying to do the Vlookup function for... Note, my function is correct, I am wondering if it is because the cells are formatted incorrectly, but even when I format them the same way the issue still persists

Formula I am using: =vlookup(A1,Sheet2!A2:C7,3,false)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client ID#[/TD]
[TD]Client Number[/TD]
[TD]*Updated Sales[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Bob's Bakery[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1021[/TD]
[TD]Bob's Bakery[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]Diane's Desserts[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated --> once again, I know the formula I am using is correct (I may not have typed it correctly here) but I know it is as I have made a sample of this and done the exact same formula and it works correctly.

 

[sheetspread]

Format is only how the numbers are displayed, it doesn't change the cell contents. If you're importing from a database, the numbers are often converted to text (test by typing =isnumber(a1 or wherever)) You can convert the text to numbers by typing a 1 in a blank cell, copying, and paste special-multiply to the range.

 

[sheetspread]

You can also apply the text or value function in the vlookup formula