any ideas why this formula does not work?

[nathan663]

=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?

 

[VoG]

Perhaps

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

 

[Andrew Poulsom]

Look here:

http://www.contextures.com/xlFunctions02.html#Trouble

 

[nathan663]

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]

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]

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

 

[Andrew Poulsom]

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))

adds zero if adding zero returns a number, otherwise does nothing.