VLOOKUP Question- Can't find a solution!

[Amburrito]

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)

Please help!!

 

[Tfox2791]

can you paste in an example from the spreadsheet? With phony names and numbers of course.

 

[Marcelo Branco]

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.

 

[Amburrito]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]StudentID
[/TD]
[TD]Name
[/TD]
[TD][/TD]
[TD]StudentID
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]=VLOOKUP(B2,$D2:$E$7,1,FALSE)
[/TD]
[TD]Richard Wright
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 96"]
<tbody>[TR]
[TD]@00589355
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Roger Waters
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]Bob Klose
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 96"]
<tbody>[TR]
[TD]@00588374
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]David Gilmour
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]David Gilmour
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 96"]
<tbody>[TR]
[TD]@00570591
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Syd Barrett
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]Roger Waters
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 96"]
<tbody>[TR]
[TD]@00589004
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Richard Wright
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD]Richard Wright
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 96"]
<tbody>[TR]
[TD]@00546320
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Nick Mason
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]Roger Waters
[/TD]
[TD][/TD]
[TD]
[TABLE="width: 96"]
<tbody>[TR]
[TD]@00580992
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Bob Klose
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Tfox2791]

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)

 

[Amburrito]

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!

 

[Amburrito]

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.

 

[DeezNuts]

Was messing around and this seems to do the trick

=INDEX($D$2:$D$7,MATCH(B2,$E$2:$E$7,0))