Excel 2007 : Inconsistent results with Lookup

[chuckbent]

I'm trying to use the Lookup - Vector Form, but it does not work consistently. Here's what I am trying to do.

My first column is student names (1,000 or so different students).
Subsequent columns are different class choices (A, B, C,... ZZ. These are the names in Row 1)
Students apply for 5 classes, which I will denote with an "x"
I will then assign them to one of their 5 choices by changing the x to an "a"
I want to add a column that will return the class name (A, B, C... or ZZ) when I make the change to "a"

Here's what my header row looks like:
Student Name, Assigned Class, A, B, C, etc

Here's what I put in the cell next to the first student's name:
=LOOKUP("a", C2:BC2, C1:BC1)

TWO PROBLEMS
1. It is inconsistent - sometimes it works perfectly, but other times I will enter an "a" and nothings happens.
2. When I "pull down" the formula to the cells below, I want C2:BC2 to advance but for C1:BC1 to remain the same.

If I can make this work I will be a hero among my fellow teachers! Can anyone help me?

[abousetta]

Hello and welcome to the forum,

Could you please update your title to reflect the problem better? Also could you upload a dummy workbook so we can add see the structure. It is not always easy for us to reproduce the a similar workbook to the one you are actually using.

Thanks.

abousetta

[chuckbent]

Absolutely. I changed the title - now let me figure out how to do a dummy workbook.

Thanks

[abousetta]

Thanks for updating the title. I will await the dummy sheet. In the meantime, in the lookup formula you need to use curly brackets for the vectors like this:

Please Login or Register  to view this content.

abousetta

[chuckbent]

Thanks - just added the curly brackets and got an error popup :-(

[abousetta]

Did you add a number next to A. You need to define which cell you are referring to (e.g. A2).

I will take a closer look at the dummy workbook when its uploaded. It will give us a better idea of what we are dealing with.

abousetta

[chuckbent]

I tried adding a number but that didn't help.

[abousetta]

I guess we need to see what your workbook structure is like. You can dummy the labels and actual numbers, but keep the structure the same.

abousetta

[chuckbent]

I attached a worksheet to my original post. Will that work?

Thanks for your help.

[abousetta]

Yes, that's fine. Let me have a look and hopefully get back to you soon.

abousetta

[abousetta]

Maybe try Index/ Match instead of Lookup:

Please Login or Register  to view this content.

Put this in B2 and drag down.

Josephine is not assigned a class and so row B7 will have an error. Do you want something to show up instead of an error (e.g. No class assigned)?

abousetta

[chuckbent]

That works great!

The error message is fine. I'll hide it with conditional formatting/white font.

Thanks so much. You made my friend's life a whole lot easier.

Am I to put a "Solved" message on my original post?

[abousetta]

You can mark the thread as Solved b going to the original post, clicking on edit then advanced and changing the prefix to SOLVED. Also if you are satisfied with my responses, reputation (scales) are always welcome.

Finally, instead of using conditional formatting, you can use IFERROR() if your friend is using Excel 2007 or later. Just a thought...

Good luck.

abousetta