The correct VLOOPUP formula?

BCK Team

Hello I am needing a formula that will pull an employee level and assign it to the alias listed. I have used differed variations of VLOOKUP and nothing seems to work.

Breakdown:

Sheet #1 will contain a list of an employee alias's, sometimes the alias will be listed multiple times. In the column "Level (Formula)" on sheet #1, I am needing the VLOOKUP formula to pull the level that matches this alias from Sheet #2.

Sheet #1:

Sheet #2 will hold a list of all employee alias's and their matching level. This is where the formula (in sheet #1) will be pulling from.

Sheet #2:

I have tried a variation of formulas:

=VLOOKUP(Alias@row, {Sheet #2 Range 1} + {Sheet #2 range 2}, 2)

=VLOOKUP(Alias@row, {Sheet #2 Range 1}:{Sheet #2 range 2}, 2)

I am not sure how to fix my formula.

Thanks again for the help :)

Heather Duff

Hi! This might be a good place to use INDEX/MATCH instead of VLOOKUP.

Try this:

=INDEX({Level Source}, MATCH(Alias@row, {Alias Source}))

Where {Level Source} is the range name for the Level column in your source sheet, and {Alias Source} is the range name for the Alias column in your source sheet.

Hope this helps!

Best,

Heather

BCK Team

Hi @Heather D!

Thanks for the fast response. It worked :)

Heather Duff

Fantastic! Happy to help.

BCK Team

@Heather D

Ok so it works for one employee alias and then spits out the error code "#No Match" when a different alias is listed.

Example:

Not sure why this is happening!

Heather Duff

Very strange! Are you sure that alias shows up in the source sheet?

Kelly Moore

Hey @BCK Team , @Heather D

Try adding a zero in the MATCH function as the search type

=INDEX({Level Source}, MATCH(Alias@row, {Alias Source},0))

BCK Team

@KDM

That worked!! Thanks