IF AND Statement Issue?

Hello!

Trying to get this if statement to work,

=IF(AND LEFT(A2,1)="L", LEFT(A2,2)="V"),Z2="Furniture", IF(AND LEFT(A2,1)="D",LEFT(A2,2)="E"),Z2="Decor",IF(AND LEFT(A2,1)="D", LEFT(A2,2)="N"),Z2="Furniture", IF(AND LEFT(A2,1)="B", LEFT(A2,2)="D"),Z2="Bed",IF(AND LEFT(A2,1)="L", LEFT(A2,2)="T"),Z2="Lighting", IF(AND LEFT(A2,1)="B" ,LEFT(A2,2)="A"),Z2="Bath", IF(AND LEFT(A2,1)="K" ,LEFT(A2,2) = "T"), Z2 = "Kitchen", IF(AND LEFT(A2,1)="H",LEFT(A2,2) = "W"), Z2 = "Hardware", IF(AND LEFT(A2,1)="F", LEFT(A2,2) = "L"), Z2 = "Flooring", Z2 = "NOPE")))))))))

So, IF the first letter in cell A2 is "L" AND the second letter in cell A2 is "V" then Z2 should equal "Furniture" in its cell.

I think I'm missing bracket or a comma or something silly, if you guys can please let me know what I need to do to get this working that would be awesome!

If you require this formula in Z2 then perhaps

Formula:

Please Login or Register  to view this content.

However have you thought about a lookup table and using a VLOOKUP?

Upload the workbook for more specific advice and manually add some typical results so we know exactly what you want.

Create a 2 column table like this:

Data Range

	E	F
1	------	------
2	LV	Furniture
3	DE	Decor
4	DN	Furniture
5	BD	Bed
6	LT	Lighting
7	BA	Bath
8	KT	Kitchen
9	HW	Hardware
10	FL	Flooring

Then the formula would be:

=IFERROR(VLOOKUP(LEFT(A2,2),E2:F10,2,0),"")

EDIT...test

Nice

You could probably condense that a little by using OR...
=IF(ORLEFT(A2,2)="LV",LEFT(A2,2)="DN"),"Furniture",........................
(may be others you can combine too.

hmm alternatively, a small table with the LEFT 2 characters in 1 column, and their "word" in the next, then use VLOOKUP()...

	A	B	C
2	LV123
3	LV	Furniture	Furniture
4	DE	Décor
5	DN	Furniture
6	BD	Bed
7	LT	Lighting
8	BA	Bath

etc
C3=VLOOKUP(LEFT(A2,2),$A$3:$B$8,2,0)

hmm interesting, I tried to edit my post, and got a blank "entry" screen, was going to say to Tony that GMTA

Tony what happens if you try to edit your post?

Originally Posted by FDibbins

Tony what happens if you try to edit your post?

I was able to edit my post.

Originally Posted by legnak

I managed to figure it out with the info you guys provided.

My pleasure to have assisted and thanks for the rep.

I still get the blank "typing" window