Lookup Table HELP

[mhartle]

Hello,

I am in need of some help with a lookup table. I don't know how to use the VLOOKUP function.

Option Subjects
(w) BIO/BIO/CHE/CIV/FRE/GEO/ICT/PMS/PHI/PHY/POL/SPA
(x) BIO/CHE/ECO/FM/GEO/HIS/LAT/LIT/PHI/PHY/PHY
(y) BUS/DRA/GER/HIS/FM/PHY/PMM/PMS/POL/SPA/MUS
(z) ART/ART/BIO/BUS/CHE/CHE/HIS/GRA/LAN/PMM/RS


This is the table. It will help me in writing the school timetable.

Here is what I want excel to do:

I enter "(w)" into a cell; then, in that same cell, excel changes it into "(w)"'s corresponding subjects BIO/BIO/CHE/CIV/FRE/GEO/ICT/PMS/PHI/PHY/POL/SPA

Your help would be much appreciated Thanks

[dredwolf]

to change it within the same cell, you will need to use VBA...is this acceptable?
Or would you prefer another cell with the result?

[mhartle]

What is VBA?

I just want to type the letter in and then it turns into the right result

I can typethe letter in one cell and the result comes into a different cell. That would be ok

[FDibbins]

you can only have 1 type of input in a cell - either a formula or a value/text, but not both.

I would suggest that you have a 2nd cell/column that returns the into you want, based on what is entered in the 1st cell/column.

If you really insist on having everything based on just that 1 cell, then, as suggested above, the only way to do that is with VBA

[mhartle]

FDibbins,

Thanks for the reply,

What would the table then look like? What would the formulas be?

[FDibbins]

dredwolf actually had you heading in the right direction, but the table would be a simple 2-column table, either on the same sheet (simpler) or on another sheet (almost as simple)

using your data above...
A...B
(w) BIO/BIO/CHE/CIV/FRE/GEO/ICT/PMS/PHI/PHY/POL/SPA
(x) BIO/CHE/ECO/FM/GEO/HIS/LAT/LIT/PHI/PHY/PHY
(y) BUS/DRA/GER/HIS/FM/PHY/PMM/PMS/POL/SPA/MUS
(z) ART/ART/BIO/BUS/CHE/CHE/HIS/GRA/LAN/PMM/RS

you would have the codes in 1 column, say column D - not sure why you need the brackets, you could just enter w, x etc
then in the 2nd column, say column E, you would have the classes

you could have A1 where the code is entered, and then in B1, the formula would be someting like...
=vlookup(A1,D1:E4,2,false)

the syntax for vlookup is...
=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

[mhartle]

Thank you FDibbins and dredwolf,

This has worked. Thank you for your time both of you.

Happy New Year

mhartle

[dredwolf]

here's the solution I came up with,(using multiple columns to allow for editing course structures easily)(attachment)
it is a long formula, but it includes all 12 (possible) classes, and lets you adjust the classes a little easier

Hope this helps

and Thank You!...just seen the other messages

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.