Multiple formulas/conditions

[drluke]

I have a spreadsheet with a list of names for which I have to do lookups to in order to get specific information. In my data table I have:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]DIVISION[/TD]
[TD]ROUTE[/TD]
[/TR]
[TR]
[TD]Smith J[/TD]
[TD]Technical[/TD]
[TD]5268[/TD]
[/TR]
[TR]
[TD]Brown RS[/TD]
[TD]Legal[/TD]
[TD]276634[/TD]
[/TR]
</tbody>[/TABLE]

In my working sheet I have a list of names in col A as follows:

John Smith
Bobby Brown

I need to (a) switch the order of first & last name (b) extract only the initial of the first name and (c) lookup e.g. the division for all but where there are 2 initials as in C3 above, somehow ignore the 2nd initial in the lookup.

If at all possible I need to do all of that in one formula. I can't change the data in my table.

Someone here has kindly helped me with (a) & (b) using the formula

=RIGHT(A2,LEN(A2)-FIND(" ",A2,1))&" "&LEFT(A2,1) but I have no idea how to add the lookup to this formula.

Any advise would be much appreciated.

 

[Peter_SSs]

Could you adapt something like this?

Excel Workbook
	A	B	C
1	NAME	DIVISION	ROUTE
2	Smith J	Technical	5268
3	Brown RS	Legal	276634
4
5
6	John Smith	Technical
7	Bobby Brown	#N/A
Lookup Name