Vlookup value error

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

deletedalien

Well-known Member
Joined
Dec 8, 2008
Messages
505
Office Version
  1. 2013
Platform
  1. Windows
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Dec 8, 2008
Messages
505
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 26, 2002
Messages
2,318
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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.
 

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.

Mr. Aladin,

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
Joined
Jun 5, 2014
Messages
1,105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,946
Members
414,417
Latest member
Nobu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top