Lookup a name if cell value first letter is in a range

[FirstVette52]

A B C D E F G H
1 Rep Student ID Last Name First Name Rep
2 L00012342 Acker John Danielle A-F
3 L00012343 Harrisuillos Mary William G-L
4 L00012344 Quhenrun Ed John M-R
5 L00012345 Voe Sue Katherine S-Z

This is what my data looks like (Student Table in Columns A,B,C,& D and a
Lookup Table in Columns F & G).

PROBLEM: If a 'Last Name' begins with a letter in a range (Column G), then
I want to assign the corresponding 'Rep' (Column F) to Column A. Is this a
possibility?

Thanks for any help you may be able to give

 

[Pete_UK]

I would suggest that you use column H to store the first letter of the
range, i.e. A, G, M and S in your example, in H2:H5.

Then in A2 you can use this formula:

=INDEX(F$2:F$5,MATCH(LEFT(C2),H$2:H$5))

and copy it down as required.

Hope this helps.

Pete