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.

 

[legalhustler]

Which range is just numeric? Which range is just text?

Column B contains standalone numeric values and standalone text. Column C contains standalone numeric values only. I would like to avoid using volatile functions like Offset as it will slow down calculations significantly.

 

[Aladin Akyurek]

Column B contains standalone numeric values and standalone text. Column C contains standalone numeric values only. I would like to avoid using volatile functions like Offset as it will slow down calculations significantly.

Define Lrow using Formulas | Name Manager as referring to:

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

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

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

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

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

Define Ivec as referring to:

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

The formula now becomes:

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

 

[legalhustler]

Define Lrow using Formulas | Name Manager as referring to:

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

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

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

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

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

Define Ivec as referring to:

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

The formula now becomes:

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

I have column headers (i.e. text) in B5 and C5 and I wanted to do this calculation in cell B2. Will this affect the formula?

I was trying not use defined name and combine each element, but can't seem to get the formula to work. I saw a test someone did on here and said defined names are 1.5 times slower than having the formulas typed in.

 

[Aladin Akyurek]

I have column headers (i.e. text) in B5 and C5 and I wanted to do this calculation in cell B2. Will this affect the formula?

No. Why don't you test?

I was trying not use defined name and combine each element, but can't seem to get the formula to work. I saw a test someone did on here and said defined names are 1.5 times slower than having the formulas typed in.

What are you claiming? That this:

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

works, but this:

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

does not?

I used Sheet1 as the sheet where the ranges occur and asked you to adjust the sheet name if it's different. Have you carried out the definitions at all?

 

[legalhustler]

No. Why don't you test?



What are you claiming? That this:

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

works, but this:

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

does not?

I used Sheet1 as the sheet where the ranges occur and asked you to adjust the sheet name if it's different. Have you carried out the definitions at all?

Originally I was saying that formulas driven in cells is 1.5x faster than formulas driven by defined names in cells.

I did try testing it by putting the formula together without defined name but couldn't proceed further because of an error thus I could not tell if it would work with the column headers (i.e text) in B5 and C5 etc.

Nevertheless, I changed the sheet references to my sheet reference tried and ran the formula (using defined names) like yours but go a N/A error. Note that now B1:B6 and C1:C6 has blank/empty cells, so I'm not sure if it'll affect B & C ranges in your formula.

 

[Aladin Akyurek]

Originally I was saying that formulas driven in cells is 1.5x faster than formulas driven by defined names.

If you believe that, why are you posting a query like "I have the following array formula and was wondering how I can make the ranges dynamic because sometimes I can have more than 995 rows or sometimes less, so the sum should be able to determine the precise range" at all?

Nevertheless, I changed the sheet references to my sheet reference tried and ran the formula like yours but go a N/A error.

That would mean that you probably did not implement the set up as I outlined.

Note that now B1:B6 and C1:C6 has blank/empty cells, so I'm not sure if it'll affect B & C ranges in your formula.

You mean B1:B5 and C1:C5 for:

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

does exclude B6 and C6.

 

[legalhustler]

If you believe that, why are you posting a query like "I have the following array formula and was wondering how I can make the ranges dynamic because sometimes I can have more than 995 rows or sometimes less, so the sum should be able to determine the precise range" at all?

I did not know that you were going to used defined names, but I thought that even if you did I could simply put the elements together and revise it, and when I tried I got an error. I tried using defined names but got an N/A error.

You mean B1:B5 and C1:C5 for:

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

does exclude B6 and C6

Let me clarify. I have column headers in row B5 and C6. I would like to make this formula in cell B2. I wasn't sure if it would affect your formula that's all.

 

[Aladin Akyurek]

I did not know that you were going to used defined names, but I thought that even if you did I could simply put the elements together and revise it, and when I tried I got an error. I tried using defined names but got an N/A error.



Let me clarify. I have column headers in row B5 and C6.

I would like to make this formula in cell B2. I wasn't sure if it would affect your formula that's all.

1) Does this formula that you posted

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

work?

2) Does the data start a B6:C6 as this formula implicates?

3) What is the name of the sheet housing the data this refers to?

 

[legalhustler]

1) Does this formula that you posted

{=SUM(IF(FREQUENCY(IF($B$6:$B$995& "|"&$C$6:$C$995<>"",MATCH($B$6:$B$995& "|"&$C$6:$C$995,$B$6:$B$995& "|"&$C$6:$C$995,0)),Ivec),1))}

work?

2) Does the data start a B6:C6 as this formula implicates?

3) What is the name of the sheet housing the data this refers to?

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

 

[legalhustler]

This is what I have for my defined names.

Lrow:

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


Brange:

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

Crange:

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

Ivec:

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

Combined and used CSE:

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

Calculations are set to manual, this should not affect the formula because other array formulas work in this setting.