Trying to lookup the same value and return it multiple times

[Chasien]

Hello,

I have a database with graduating students and their degree information. There is a column with the degree abbreviation (ba, bs, bse, etc.) and one with the full degree title (Bachelor of Arts, Bachelor of Science, etc.). There are nine different degree abbreviations that we use and generally the database has about 600 different students. I'm trying to find if there is a way to lookup the full degree title from the degree abbreviation. I've tried using VLOOKUP and INDEX/MATCH but in each case the data returned is only the first instance of it and everything else comes back as #N/A.

I've been trying to figure out if this is possible, but I haven't been having much luck. I know I can't be the only person who has needed this, but I'm at a loss for what wording to look for next. I don't know if there is a function/formula that would work for this or if I would have to do something more difficult. If someone could even point me in the right direction I would be very grateful.

The other option that was mentioned to me was to make the column drop down boxes, but I'm not sure that would work as well or as fast.

Thanks in advance for any help!
Chasien

[mrice]

You might be using relative addresses in your functions. You might need to add $'s to fix the range in which you are looking up.

[Chasien]

vlookup-index testing.xlsx

Hello,
Sorry I didn't respond sooner, I was having computer troubles over the weekend. I tried to attach a copy of the file I am working on, so hopefully it worked. I did try to add the $ in with the formula, but it didn't seem to make a difference. It will pull the correct degree for the first abbreviation, but if the same abbreviation comes up again it pulls an #N/A error.
This is the code I'm using: =VLOOKUP($F$14,$A$5:$B$8,2,FALSE). I tried to do an Index Match one as well on the first sheet, which does the same thing. This is the other code I tried: =INDEX($D$15:$D$18,MATCH(C3,$C$15:$C$18,0)).

[dredwolf]

One of the reasons you are getting #N/A! errors is that the lookup tables have leading/trailing spaces in the information, try something like this :
(ARRAY FORMULA, use Cntrl+Shift+Enter, not just Enter) G11:

=VLOOKUP(TRIM($F15),TRIM($A$2:$B$7),2,FALSE)

Drag down

Hope this helps

[Chasien]

It works! Thank you so much! It didn't work at first and then I reread it and used the Cntrl+Shift+Enter and it worked like a charm.
Thanks again!

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"