# Vlookup value error

#### legalhustler

##### Well-known Member
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.

Last edited:

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### deletedalien

##### Well-known Member
Are you currently getting #N/A Error?

or what is it exactly that is happening?

Also this looks like it might be a general formatted cell so excel doesn't see it as a number...

Try that part first,

insert a helper cell in there with just =(MID(A2,22,4) and then do a regular vlookup to the result of this

##### MrExcel MVP
Care to state in a straight manner what you require instead of formula talk? If A:B is your look up table, what is the look up value and the corresponding result value?

#### deletedalien

##### Well-known Member
Wait Wait Wait.. i JUST noticed this part

you cant do this in a Vlookup...

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

Also you are looking for the MID portion in your lookup Value and trying to match it to a full cell value "A" but them the mid value of "B" to do what?

and asking it to return the value of the second column but vlookup will return the FULL value not the mid value,

Last edited:

#### legalhustler

##### Well-known Member

Care to state in a straight manner what you require instead of formula talk? If A:B is your look up table, what is the look up value and the corresponding result value?

The lookup value is 2473 (in red) and the corresponding result should be 610000 (from column B). Thanks.

I will provide sample data when I get home tonight.

Last edited:

#### Asala42

##### Well-known Member
I'm a little confused as well.

If it were me I'd try to keep things simple:

To isolate red: In B2 =MID(A2,12,4)
To isolate blue: In C2 =MID(A2,19,6)

Then your vlookups should be fairly easy, given any 4 digit red code in lookup cell Z2:
=VLOOKUP(Z2,\$B\$2:\$C\$1000,2,0)
will find any corresponding 6-digit red code from your data set.

##### MrExcel MVP

The lookup value is 2473 (in red) and the corresponding result should be 610000 (from column B). Thanks.

I will provide sample data when I get home tonight.

The difficulty with this answer is that you fail to distinguish a look up value (like 2473) from the table in which you want to look for it. This is the probably the reason why this matter turns up again and again in your posts.

#### burneggroll

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

No, you have a typo. Should be MID(A2,12,4).

Last edited:

#### legalhustler

##### Well-known Member
The difficulty with this answer is that you fail to distinguish a look up value (like 2473) from the table in which you want to look for it. This is the probably the reason why this matter turns up again and again in your posts.

The lookup table is the entire array of values in column A and the lookup value is single value (like 2473) within column A.

I personally resolved my issue of extracting unique GL's and summing their amount (see columns D & E) in the link below. My next goal is to extract GL's by fund (See Expected Resulted in columns G & H). I think it's a matter of tweaking my formula a little. I would like to do it without a helper column if possible, but it's okay if that can't be done.

https://app.box.com/s/q37zr5emh2n0fdcev7k0z9qf9p6iypfv

Thanks!

Last edited:

#### legalhustler

##### Well-known Member
Wait Wait Wait.. i JUST noticed this part

you cant do this in a Vlookup...

Also you are looking for the MID portion in your lookup Value and trying to match it to a full cell value "A" but them the mid value of "B" to do what?

and asking it to return the value of the second column but vlookup will return the FULL value not the mid value,

Let me try to explain a little more clearly.

I have several rows of text strings in column A and I have GL's in column B (see sample data). I am trying to individually extract each fund (i.e. 2473) from column A and looks it up within the whole range in column A and return it's corresponding GL from column B. My goal is to extract each GL AND it's unique Fund (see Expected Results in sample data). Hope this helps.

Thanks.

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,681
Messages
5,838,780
Members
430,568
Latest member
bortey

### 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.

### Which adblocker are you using?

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

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