Formula to look up name and return the tier

[Jamesraywebber85]

I have in Worksheets("TSA Request").Range("F4") the name of an agent lets say its "Joe Smith" and on Worksheets("CS Data") in column F is their 1st name and column E is their last name - I want for it to search for the agents name and return the value in cell Worksheets("TSA Request").Range("F3"). Any help would be greatly appreciated!

[63falcondude]

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Sam Capricci]

Hi neighbor, a sample sheet would go a long way to getting what you need.

[Jamesraywebber85]

Here is a sample of my workbook - some may not work because I have a macro pulling the user name for the agent

[63falcondude]

Based off of the sample that you shared in post #4, what are the desired results of the formula?

I don't see a Ray Webber anywhere in the CS Data sheet.

[Jamesraywebber85]

Correct - like I said I have a VBA code that is pulling the user name - so lets say Ray Webber was in cell F4 - I want it to find the name on sheet CS Data in cells (F+E) match the name then look in cell K to find the tier of the agent and put it on "TSA Request" cell F3

[Sam Capricci]

well vlookups would generally work but if the names are not the same then you won't have matches. the only way they can match is if you have a reference table somewhere that says Ray Webber is equal to Webber, James.
but this for example would work in F3 of the TSA request =VLOOKUP($F$4,'CS Data'!$G:$K,5,FALSE) but based on your data it will return #N/A because they don't match.

[63falcondude]

For the sake of a sample, you should manually make things look as they do in the real thing.

Here are two options:

1) F3 =INDEX('CS Data'!K:K,MATCH(MID(F4,FIND(" ",F4)+1,100)&", "&LEFT(F4,FIND(" ",F4)-1),'CS Data'!G:G,0))

2) F3 =INDEX('CS Data'!K:K,INDEX(MATCH(F4,'CS Data'!F:F&" "&'CS Data'!E:E,0),0))

If you chose option 2, make sure to limit your ranges to something as small as possible.