getting error using =index formula

**Elainefish** · 06-18-2013, 03:08 AM

I am having problem with my =INDEX formula
I am using =INDEX(L2:L30;MATCH(B4;K2:K30;0))
the problem is that not all shows up. if the value is letter, the formula works fine but if it is number the i am getting #N/A results
fyi: I am trimming the text first, I tried to use =left formula and =trim formula but both have the same problem

TRIM FORMULA PROBLEM.xlsx

**zbor** · 06-18-2013, 03:13 AM

When converting you should get numbers (not text) then it will work (i.e. =--LEFT(A4, 1)

Other way is like this (comfirmed with ctrl+shift+enter):

Formula:

Please Login or Register  to view this content.

**benishiryo** · 06-18-2013, 03:17 AM

hi Elainefish. when you do a LEFT/RIGHT/MID, you are making numbers become texts too. so column B are all texts while column K contains numbers & texts. few ways:
1. array formula in C3
=INDEX($L$2:$L$30,MATCH(B3,$K$2:$K$30&"",0))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

2. change formula in B3:
=IFERROR(LEFT(A3,1)+0,LEFT(A3,1))

3. change column K to be all Text format.