hi,

I'm brand new to this site and just signed up because no one at my job knows how to use vlookup and I'm running into problems. I update a daily spreadsheet for two sales reps I work with based on data that I pull from 'salesforce' our online sales database. Salesforce runs a daily report for me that is exported to excel (2000). Based on the report from salesforce, I update the existing sales spreadsheet for our reps with dates, active prospects, clients we are pursuing, etc. So, every time I export the data from salesforce in excel, I copy and paste it into the existing sales spreadsheet as a tab called 'sfpull'. I am trying to create a vlookup formula using the 'sfpull' info so it automatically updates the existing data. Here is what I have done thus far:
- 'defined' the fields of data I need from 'sfpull' as 'pull'
- each rep (2) has there own tab with company names, dates, etc. I created an extra column in each reps' spreadsheet with the following formula: =vlookup(c2 (refers to the company name),pull, 3 (column that I want the formula to pull from in 'sfpull'), false) so it's =vlookup(c2, pull,3,false) and the return is 'n/a'

am I not supposed to use this type of formula when comparing text? Can someone please help me?

thanks,

Rose

Do you feel comfortable posting a small example of this; you can eliminate any identifiable data. If so please attach the spreadsheet data and I can show you how to do this.

Its just too hard to type, I need to see it.

Hi! Thanks for your response. I've attached both print screens of one of the sales rep's tabs that shows the vlookup formula and the tab that the data should come from.

If you have a problem seeing either, do let me know.

Thanks!
Rose

Well you need a unique field that is common to both parts of your spreadsheet and that unique field needs to be in the first column of the data you are trying to pull. Also you’re not defining your data field correctly. Simply telling excel look in this tab is not enough, you need to tell it exactly where to look. If you click the Fx button it is easier to follow the along and tell excel where to look and what you are looking for. The rest you seem to have correct.

Take a look at the example that I am attaching and try to follow that method. Then if you still need more help let me know.

It appears you defined the named range as columns A, B and C. The column with the matching "company name" in it is column B. So rename your named range as only columns B and C. The name you use must NOT be the same as the SHEET TAB NAME, that's just asking for trouble when you do that.

You can do it without the named range, like so:

=VLOOKUP(C2, SFPULL!$B$1:$C$1000,2,FALSE)

Your example, C2, by the way, is an empty cell, so you're going to get an error. But when you copy that formula down, you will get the FIRST match for the companies that do match.

Note, FIRST match only. Your sample shows multiple comments for A.T.T. Wireless. You won't get them all with that formula.

To get ALL the A.T.T. Wireless comments to appear will require an array structure, which I can show you, but it's now what you asked.

By the way, VLOOKUP is not the best function for this kind of thing, the best of all is INDEX/MATCH as it is statistically the fastest of the two. Here's the INDEX/MATCH version of the same formula I gave you above.

=INDEX(SFPULL!$C$1:$C$1000,MATCH(C2,SFPULL!$B$1:$B $1000,0))

The huge benefit of INDEX/MATCH over VLOOKUP is the VLOOKUP only works for matching data to the right of a searched column. INDEX/MATCH works in all directions.