any ideas why this formula does not work?

nathan663

Board Regular
Joined
Dec 3, 2008
Messages
153
=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?
 

Some videos you may like

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
Joined
Jun 19, 2002
Messages
63,650
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
Joined
Dec 3, 2008
Messages
153
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
Joined
Jul 21, 2002
Messages
73,092
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")
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,952
Messages
5,508,331
Members
408,680
Latest member
Daven13

This Week's Hot Topics

Top