Thread: Cannot solve vlookup/mid function problem...

Hello all,

I built a spreadsheet to automatically decode part numbers and can't get a few specific values to show up. I have no idea why. The problem lies in between a mid function and the lookup. The mid function for some reason results in an output that breaks the lookup and returns #N/A. The intersting part is that I use the same formula througout the whole sheet and 70% of them work. The other 30%...I have no idea.

I think it is a formatting issue, as when i write over the mid function with a number it works. However, I can't use "+0" or similar tricks because the result can be either a number or letter. I've tried every way I can think of to reformat but am having no luck.

I've attached the file for reference. You can see that in the "Flash Decode" sheet, cells C5, C9, and C11 return #N/A but the others cells work fine. I just can't get the mid function hin these cells to return a usuable value. Also, the problem cells work fine if the mid function returns a letter, just not with numbers.

Any help is much appreciated! I feel like I need to break things

P.S. attachment should work now

Hi and welcome to the board

Although I use a converter I cannot open your sheet. Could you post it again please? Thx

Company DRM causing me problems...I'll fix then re-upload.

This is going to sound brutal, but I don't think I have ever seen such a badly organised file and coding system before.

Your formula is different in each row and therefore cannot be dragged to get the results you look for.
For example with this in C4
=VLOOKUP($B4,Sheet3!A2:B2,2,FALSE)
And this in C5
=VLOOKUP($B5,Sheet3!A5:B5,2,FALSE)
You might as well type the answers manually!

This approach will ""fix"" your immediate problem
=VLOOKUP(IF(ISERROR($B5*1),$B5,$B5*1),Sheet3!A5:B5,2,FALSE)

However.
Try breaking your lookup list into seperate sections/groups, 1 to 17,18. then refer to these by named ranges as required. (See "Sheet3")

For Example
"CodeGroup3"
Refers To:

=Sheet3!$G:$G

Where there are illegal characters or spaces the name needs a little modification
"CodeGroup4,5" becomes "CodeGroup4_5"
Refers To:

=Sheet3!$K:$K

We can now add a column to your sheet "Flash Decode" with the header "CodeGroup"
In this column we can now list the groups "1", "2", "3,4", etc...
By concatenating the header and the code group we can now use INDIRECT() to reference the Named Ranges, or directly, the actual string in "Sheet3".
(Unfortunately INDIRECT() doesn't like dynamic named ranges so these names are possibly easiest refering to whole columns.)

So in B4 this formula

=OFFSET(INDEX(Sheet3!$1:$1,1,MATCH($A$3&$A4,Sheet3!$1:$1,0)),0,1)

Drag/Fill Down
This will find, for example, "CodeGroup3,4" ($A$3&$A7) in "Sheet3" Row 1 and return the value offset by 0 rows and 1 columns. In this example "Small Classification".

In D4

=OFFSET(INDEX(INDIRECT(SUBSTITUTE($A$3&$A4,",","_")),MATCH(IF(ISERROR($C4*1),$C4,$C4*1),INDIRECT(SUBSTITUTE($A$3&$A4,",","_")),0),1),0,1)

Drag/Fill Down

Let's break this down
1/. SUBSTITUTE($A$3&$A4,",","_")
This replaces any commas in the concatenated group name with underscores.
So for example "CodeGroup3,4" becomes "CodeGroup3_4"

2/. This can now refer to the named range by using ....
INDIRECT(SUBSTITUTE($A$3&$A4,",","_"))

3/. Now using INDEX(range,row,column)

range = INDIRECT(SUBSTITUTE($A$3&$A4,",","_"))

row = MATCH(value,range,return exact match =0)
MATCH(IF(ISERROR($C4*1),$C4,$C4*1),INDIRECT(SUBSTITUTE($A$3&$A4,",","_")),0)
Note
IF(ISERROR($C4*1),$C4,$C4*1) chooses whether to read your "Code" (Column C) value as text or a number.

column = the column to return the result from, in this case there is only one column in the named ranges so this is always 1

This is is written as
INDEX(INDIRECT(SUBSTITUTE($A$3&$A4,",","_")),MATCH(IF(ISERROR($C4*1),$C4,$C4*1),INDIRECT(SUBSTITUTE( $A$3&$A4,",","_")),0),1)

4/. Finally Offset("index/match formula" ,0 (rows),1 (cols))
Note that #N/A will be returned if no match is found.


The way you are breaking down the Part# is not really logical, but if this is how the existing numbers are "formatted", then there might be nothing that can be done to improve the situation.
The fact that this returns 0 & 00 as potential codes is not easy to control.
The reason for your original formula failing is the lookup is trying to find e.g. "9" as a string not 9 as a number as it is in the lookup list(s).

See this link Contextures - Excel Data Validation -- Create Dependent Lists for a fuller explanation and more examples.

This is not nescessarily the best way to tackle your problem, but it is possibly the easiest way to present the results as you seem to want.

Hope this helps.