Vlookup value error

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,160
Office Version
  1. 365
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:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top