VLOOKUP Question- Can't find a solution!

Amburrito

New Member
Joined
Sep 3, 2014
Messages
4
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!!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
[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.
 
Upvote 0
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)
 
Upvote 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!
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,587
Members
452,860
Latest member
jroberts02

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top