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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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

ADVERTISEMENT

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,324
Messages
5,658,157
Members
418,431
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top