My goal is to make the rank appear when I enter the name of the staff in the shift schedule. For example If I enter Andrew in cell F2, A (the rank of Andrew) appears in cell E2. enter image description here I tried to make use of the VLOOKUP function and I believe that I'm tinkering with the right function, but I just can't get it to work.
Any help will be extremely helpful. Thank you!!

4 Answers 4


Use INDEX and MATCH functions. In E2, enter the formula:


Or use XLOOKUP() like so:

=XLOOKUP(F2,  $B$2:$B$6,  $A$2:$A$6)

It has some options I have not used, but can, for example, let you give a custom error message if "Andrew" is not present in the source. Perhaps something like "Did not find staff member. Perhaps the name is spelled incorrectly." which can be much more useful than an Excel error, to users that is.

You could actually do this with VLOOKUP() if you really wish. Like all "you could actually" things it would use a trick. Actually, there are several ways to do it, this is just the slickest, in my opinion. I recommend XLOOKUP() though since it needs no tricks and offers the useful error message feature. Still, if ever useful to you:

=VLOOKUP(F2, INDEX($A$2:$B$6,, {2,1} ), 2, FALSE)

INDEX() will take those two columns and rearrange them so that the staff member's name column is FIRST, not second. So the table is acceptable to VLOOKUP() as the lookup column (the names) is the now the leftmost column.

If they were in, say, columns A and G, you'd just use the range $A$2:$G$6 in INDEX() and then give it the two columns you want, 1 and 7, in the order {7,1} and VLOOKUP() would be happy.

INDEX() is the gift that keeps on giving. Look at this question for a similar, but more interesting use of it: How to merge multiple Mac excel files all with headers that are the same but out of order

There we used column headers to rearrange table columns to match a standard order. Same idea for pretty different situations. The gift that keeps on giving!


Please check if the answers from Reddy and Jeorje are helpful to you.
If you still want to use the VLOOKUP function to do this, I suggest you manually swap the Name column with the Rank column, then everything becomes simpler:


enter image description here

  1. In VlookUp see that Table you use to look-up, must contain the first column as Name of Employee

  2. That column must be sorted on Employee

  3. If there are similar names, again it will give wrong answer.

  4. Ideal thing is table must be arranged on UNIQUE EMPLOYEE NUMBER, and not on Names. All other columns of data can be added like Name, Position, Sex, Age, Grade, Date of joining......and all..


You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .