# VLOOKUP Question- Can't find a solution!

#### Amburrito

##### New Member
I've been trying to researching the net to try and solve this problem on my own for about a week now- I still have not figured it out yet! Hopefully someone else more experienced than I can tell me what I'm doing wrong!

What I've tried so far: Checking for spacing issues (there are none), checking to see if the names match either other with true/false (they all match as "true"), checking to see if my reference cells are the right format (text, vs general, vs number, etc), I've tried replacing the "@" symbol in the student IDs with a letter just in case it might be that- but that didn't work.

I'm a self-taught excel user, so I'm thinking it's probably something simple that I'm doing wrong.

I have alphanumeric student IDs (i.e. @0123456) that need to match to cells that contain the first and last names of the students (i.e. Sue Smith).

My referenced table array contains the alphanumeric IDs in column A, and student names in column B.

My other sheet contains student names that must remain in the order they're in, and I want the appropriate ID to match to the name. Perhaps there might also be another work around if VLOOKUP doesn't work for this?

Here's a sample formula: =VLOOKUP(B1,'Student IDs'!\$A\$1:\$B\$107,1,FALSE)

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
can you paste in an example from the spreadsheet? With phony names and numbers of course.

My referenced table array contains the alphanumeric IDs in column A, and student names in column B.

My other sheet contains student names that must remain in the order they're in, and I want the appropriate ID to match to the name. Perhaps there might also be another work around if VLOOKUP doesn't work for this?

Not sure if I understood your data setup.

See if this does what you need

=INDEX('Students IDs'!\$A\$1:\$A\$107,MATCH(B1,'Students IDs'!\$B\$1:\$B\$107,0))

Hope this helps

M.

A
B
C
D
E
1
StudentID
Name
StudentID
Name
2
=VLOOKUP(B2,\$D2:\$E\$7,1,FALSE)
Richard Wright

 @00589355

<tbody>
</tbody>
Roger Waters
3
Bob Klose

 @00588374

<tbody>
</tbody>
David Gilmour
4
David Gilmour

 @00570591

<tbody>
</tbody>
Syd Barrett
5
Roger Waters

 @00589004

<tbody>
</tbody>
Richard Wright
6
Richard Wright

 @00546320

<tbody>
</tbody>
Nick Mason
7
Roger Waters

 @00580992

<tbody>
</tbody>
Bob Klose

<tbody>
</tbody>

Here's a sample I made- I obviously couldn't use the sensitive information. But it would sort of be referenced this way.

Pink Floyd members may make excellent students. Anyway, the vlookup formula above wouldnt work for the following reasons:
1. Your look up value B2 needs to be in the first column position in the lookup array. (Your array would need to be E2:F7)
2. Vlookup will not work backwards you would need to use the index and match functions for that
3. A simple fix may be to make the cells in F2:F7 = D2:D7 then use the following =vlookup(B2,E2:F7,2,0)

Wow, I'm seriously overworked. I knew as soon as I posted to this forum that the answer would come to me! The student IDs I was looking to match up were in the first instead of the second column, so they couldn't be referenced correctly!! Sometimes, it's the simplest things.... Thanks for the help!

I was trying to think of random names, but since my brain wasn't working, and I was listening to Dark Side of the Moon, it seemed easier...haha.

Was messing around and this seems to do the trick
Code:
``=INDEX(\$D\$2:\$D\$7,MATCH(B2,\$E\$2:\$E\$7,0))``

Last edited:

Replies
10
Views
395
Replies
1
Views
107
Replies
3
Views
1K
Replies
8
Views
325
Replies
11
Views
457

1,202,984
Messages
6,052,914
Members
444,612
Latest member
FajnaAli

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back