# any ideas why this formula does not work?

#### nathan663

##### Board Regular
=IF(B2<>"Exclude",IF(ISERROR(VLOOKUP(AI2,'MI Ref'!O:P,2,FALSE)),"Other",VLOOKUP(AI2,'MI Ref'!O:P,2,FALSE)),"Exclude")

On the whole it works fine but for a small detail.

In AI2 this is a 3 digit text cell. Either 3 letters (ABC) or 3 numbers (123)
If the cell contains 3 letters the formula works fine. If it contains numbers the vlookup can not find the correct response to pull back. instead it returns "Other".

I can only think there is something wrong with the format of the cell to do with the number. but can't seem to identify exactly what is wrong... any ideas?

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### VoG

##### Legend
Perhaps

=IF(B2<>"Exclude",IF(ISERROR(VLOOKUP(AI2&"",'MI Ref'!O:P,2,FALSE)),"Other",VLOOKUP(AI2&"",'MI Ref'!O:P,2,FALSE)),"Exclude")

#### nathan663

##### Board Regular
ok, this is the actual formula, i tried shortening it in the previous post.

IF(B3<>"Exclude",IF(ISERROR(VLOOKUP(LEFT(W3,3),'MI Ref'!O:P,2,FALSE)),"Other",VLOOKUP(LEFT(W3,3),'MI Ref'!O:P,2,FALSE)),"Exclude")

#### Andrew Poulsom

##### MrExcel MVP
Your lookup range clearly contains a mixture of numbers and text. Try:

=IF(B3<>"Exclude",IF(ISERROR(VLOOKUP(IF(ISNUMBER(LEFT(W3,3)+0),LEFT(W3,3)+0,LEFT(W3,3)),'MI Ref'!O:P,2,FALSE)),"Other",VLOOKUP(IF(ISNUMBER(LEFT(W3,3)+0),LEFT(W3,3)+0,LEFT(W3,3)),'MI Ref'!O:P,2,FALSE)),"Exclude")

#### nathan663

##### Board Regular
works perfectly... i dont completely understand all of it but thank you

#### Andrew Poulsom

##### MrExcel MVP
works perfectly... i dont completely understand all of it but thank you
The LEFT function returns a string. If the string looks like a number it can be converted to a number by adding zero. Adding zero will fail (with #VALUE!) if the string looks like a number. So this part of the formula:

IF(ISNUMBER(LEFT(W3,3)+0),LEFT(W3,3)+0,LEFT(W3,3))