Vlookup when the names don't match

[brfunk15]

Wow trying to put a title to this was hard. So here is the issue and I have been racking my brain to figure it out and I am at a loss. I have a productivity report and I need to look up how many contacts the rep did from the previous day on a different tab. This Report is continually added to so I have to concatenate the name + date to make sure I am pulling the correct data for that date.

The problem is my initial lookup value for the name is their full name and the tab with the data only has their first name and last initial. I know a Right/Left function could help but the concatenate function messes all of that up.

A5: The name of the rep
B1: The Date

Right now I have been manually finishing the last name of each rep but it is becoming too time consuming. Below is the formula I have been using when manually filling in the last name.
=iferror(vlookup((concatenate(A5,$B$1)),Intercom!A:E,4,False),"")

 

[Joe4]

I think seeing some examples of all the involved data here would be helpful.

 

[wideboydixon]

How about:

=IFERROR(VLOOKUP((CONCATENATE(LEFT(A5,FIND(" ",A5)+1),$B$1)),Intercom!A:E,4,FALSE),"")

?

WBD

 

[brfunk15]

This is the main sheet on the report

-- removed inline image ---


This is the Sheet with the Data. Column D is the number I am trying to pull

 

[Joe4]

Your attempt to post an image failed. Were you attempting to use on of the tools my link references?
I would recommend trying to get it to work in the "Test Here" forum before posting it to this thread, to make sure you are using it correctly.

 

[brfunk15]

Well, that didn't work! Let's see if this works. Below should be an image of the main page on the report.

 

[brfunk15]

Sorry, so new to this! I didn't realize there was a test forum. I am so sorry everyone!

 

[brfunk15]

This works!!! Ill have to study this formula to figure it out. Thank you so much, you just saved me hours a week of work!