See attached file...I'm having trouble returning a value for the Alpha-numberics that begin with DMR, which should return 'Dishwasher'. All other values are correct, I'm sure it has to do with the character range but I'm a little stumped.
Last edited by mikera; 01-28-2010 at 04:05 PM.
try this in B2, copy down:
=VLOOKUP(IF(ISNUMBER(MID(A2,3,1)+0), LEFT(A2,2), LEFT(A2,3)), $D$2:$F$10, 3, 0)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Hi Mikera, change your formula to:
=VLOOKUP(IF(ISNUMBER(MID(A2,3,1)+0),LEFT(A2,2),LEFT(A2,3)),$D$2:$F$10,3,FALSE)
When you use the MID function it returns a text string, not a value. The text "3" is not equal to the number 3. I also moved the red 3 and two around to work properly.
Using your posted workbook....try this:
Copy that formula down through B10Code:B2: =VLOOKUP(LEFT(A2,3-ISNUMBER(--MID(A2,3,1))),$D$2:$F$10,3,FALSE)
Notice that the MID function returns text....so the ISNUMBER(MID(something))
construct will always be false. I used the dbl-minus to convert the MID value
to a number (if possible)
--"2" becomes 2
--"M" becomes #VALUE!
Dos that help?
Last edited by Ron Coderre; 01-28-2010 at 03:16 PM.
Both options worked, thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks