Vlookup value error

[legalhustler]

I have a text string in column A and a 6 digit value in column B. I am trying to return the values in green.

..........................................Column A............................................................................Column B.........

201506 075 2473000610000000000000001298705828DE F017 ................................................610000
201506 075 2480000298500000000000002705814212DB F017 A .............................................298500


I have the following formula but it returns #Value error.

VLOOKUP(MID(A2,22,4),MID($A$2:$B$2,22,4),2,0)

This part of the formula MID(A2,22,4) is looking at the values in red.

Can someone help. And No I simply cannot do a mid of column A and get the 6 digit value b/c column B can have different values.

 

[Aladin Akyurek]

1) Yes it works and I get a value result. The result is in cell B2, above the data.

2) Yes the data starts at B6 and C6

3) BFY is the sheet name

Upon questioning you asserted that C6:C995 houses numeric data (data of which ISNUMBER is TRUE). By the way, you should not anything unrelated after row 995 of BFY.

Define Lrow using Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,BFY!$C:$C)

Define Brange (or a more descriptive name) as referring to:

=BFY!$B$6:INDEX(BFY!$B:$B,Lrow)

Define Crange (or a more descriptive name) as referring to"\:

=BFY!$C$6:INDEX(BFY!$C:$C,Lrow)

Define Ivec as referring to:

=ROW(Crange)-ROW(INDEX(Crange,1,1))+1

The foregoing requires that it's implemented to the letter...

The formula now becomes:Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Brange&"|"&Crange<>"",
       MATCH(Brange&"|"&Crange,Brange&"|"&Crange,0)),Ivec),1))

You keep coming back with B2... You can do anything you wish in B2 for the definitions do not include B2.Note. Please do not quote everything from my reply if you feel like replying to it.

 

[legalhustler]

One other thing I should mention. B2:B995 contains standalone numeric values AND standalone text. B2:B995 has formulas in them and results in standalone numeric values and standalone text. C2:C995 has formulas in them and results in standalone numeric values only.

 

[Aladin Akyurek]

One other thing I should mention. B2:B995 contains standalone numeric values AND standalone text. B2:B995 has formulas in them and results in standalone numeric values and standalone text. C2:C995 has formulas in them and results in standalone numeric values only.

All this has been already discussed. Lrow is based on the range in column C. The data start at B6:C6, as reviewed numerous times.

 

[legalhustler]

All this has been already discussed. Lrow is based on the range in column C. The data start at B6:C6, as reviewed numerous times.

Yes I know, i'm just pointing out to you if maybe the formulas in those ranges is causing the error and not giving me the results. I guess not.

 

[Aladin Akyurek]

Yes I know, i'm just pointing out to you if maybe the formulas in those ranges is causing the error and not giving me the results. I guess not.

One thing that you can do yourself is to check your own supposition that column C is numeric. That's done easily:

=ISNUMBER(C6)

copied down...

 

[legalhustler]

One thing that you can do yourself is to check your own supposition that column C is numeric. That's done easily:

=ISNUMBER(C6)

copied down...

Thanks for the tip. It looks like all the values in column B & C are text not numbers! It's interesting that formula driven result values (i.e. 201021010) are shown as text instead of number. I think we need to amend the Lrow, Brange and Crange part of your formula so that it looks for text. Let me know.

 

[Aladin Akyurek]

Thanks for the tip. It looks like all the values in column B & C are text not numbers! It's interesting that formula driven result values (i.e. 201021010) are shown as text instead of number. I think we need to amend the Lrow, Brange and Crange part of your formula so that it looks for text. Let me know.

Change Lrow to:

=MATCH(REPT("z",255),BFY!$C:$C)