# Calling value in a certain offset of a searched value

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`. 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!!

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

``````=INDEX(\$A\$2:\$A\$6,MATCH(F2,\$B\$2:\$B\$6,0))
``````

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!

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:

``````=IFERROR(VLOOKUP(F2,\$A\$2:\$B\$3,2,FALSE),"")
``````

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..

Thanks.