Vlookup combined with another excel function to return a value depending on a pre-defined range

[p_anna]

Hi, I wonder if anybody could help with this one:

You have a table with invoice numbers and amounts by vendor. The job is to add a "User" column into this table using vlookup and/or other excel functions showing User1/User2/User3 linked to each vendor name, when you have a reference table on a 2nd worksheet saying that User1 are vendors whose name starts with A-D, User2 are vendors whose name starts with E-N and the rest will be User3.

What I did was to take the User name column in the table and separate their very first characters of their names by using the Text to Columns tool, and on the 2nd sheet list all the ABC and assign User1/User2/User3 to all the letters and then link them with a vlookup but I guess this is not the best solution.... Any idea welcome !

Thanks a million.

 

[j_unsuitable]

Hey

You could also do it withouth a 2nd worksheet using something like:

=IF(LEFT(A1,1)<"E","User 1",IF(LEFT(A1,1)<"O","User 2","User 3"))

But if you still want to use the 2nd sheet you could use something like

=vlookup(left(A1,1)...

Julian

 

[p_anna]

Thanks Julian !