IF statement or Macro in Excel - helppp?

Question

Hi,
I have a "IF" statement that does the following
=IF(L2=C2,B2,IF(L2=C3,B3,IF(L2=C4,B4,.......... - I cannot use this formula because I have more than 64 nested values
So, If I type a particular text in Column L, Col F should fill up with the corresponding value - from the info range given in Col B.
In other words, if I type a name of a County in a column, the previous column on left should fill up with the name of the State in which it is found - from the range of States I have in a different column.

If this question makes sense to anyone, please respond - it could either be a "If" statement or "Macro".
Only problem is, I have more than 64 nested if's - so if you can find another function or make relevant suggestions, that would be great.

Thanks.

Answer 1

If it's feasible to put the column B data to the right of column C (for example in column D), then you could use VLOOKUP:

=VLOOKUP(L2, $C$2:$D$100, 2, FALSE)

So if L2=C2, it will show what's in D2, if L3=C3 then D3, and so on.

If it's not practical to move the B values to the right of the C values, then you can use MATCH and INDEX:

=INDEX($B$2:$B$100, MATCH(L2, $C$2:$C$100, 0))

- this finds the position of L2 in the column C, and returns the value at the same location in column B.

One other option, but this requires the column C values to be in ascending order:

=LOOKUP(L2, $C$2:$C$100, $B$2:$B$100)

If you need more info, add more details or e-mail.
Good luck.
______
EDIT:
Scratch the last one. You're looking for an exact match, but LOOKUP matches the largest value that is less than or equal.

So MATCH and INDEX is probably your best bet, unless you can move the columns around, in which case you can also use VLOOKUP. Also note the ",0" in the MATCH function, which will make it look for an exact match.
.

Answer 2

I would suggest going into the VBA behind the form, and using a 'Select case' structure for this instead of the nested 'ifs'. Any time you get more than 3 4 or 5 nested ifs, chances are there is either another way to do what you want that is more efficient, or you are going about it wrong. Just go into the VBA, and do something like

Select case L2
Case C2
B2
Case C3
B3
....

Here is a link to a website that will probably be very helpful :
http://www.ozgrid.com/VBA/select-case.htm

Good luck.

Answer 3

My suggestion is pretty simple. You need to create some dependent lists. Excel makes that pretty easy if you know how.

Check out the website below. It is very good at explaining how to do this. I'd explain it on here, but with the good diagrams they have it doesn't make sense to.

http://www.contextures.com/xlDataVal02.html

Either that or create a table kind of like aladou suggested and use the VLOOKUP function.

Answer 4

You can pragmatically do this in the Macro by coding the VB yourself. Instead of using an "IF" statement use a SELECT CASE statement. Google "Excel VBA Select Case" and you should be able to find some example code to help you...