VLOOKUP on Alphanumeric

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
It appears that when doing a reconciliation of a listing of stocks and bonds, where the common identifier is a alphanumeric entry, it is not possible to use VLOOKUP (it seems to only work with numbers/values).

Any suggestions?

What I am attempting to do is take two worksheets in a workbook and match off the price per unit and quantity based off of a common identifer (the data in both sheets are in a different order). There are approximately 600 stocks and bonds to be matched in each sheet.

Any assistance would be greatly appreciated.

Thanks.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
vlookup will work with any combination. i have found that when doing a vlookup within the same workbook it can get a little messy.
what i did to get around this was add an absoulute reference to the data range.

ex
=VLOOKUP(H3,Original!$A$2:$C$994,3,FALSE)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-04 15:22, dtaylor wrote:
vlookup will work with any combination. i have found that when doing a vlookup within the same workbook it can get a little messy.
what i did to get around this was add an absoulute reference to the data range.

ex
=VLOOKUP(H3,Original!$A$2:$C$994,3,FALSE)

That "vlookup will work with any combination," is indeed true.

However, where the lookup table is makes no difference. That you must lock the definite range reference for the lookup table is required when you are copying the formula.

fmaresca can better provide a small sample along with expected results. & elaborate a bit more on the required computations.
 

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
Actually I did something similar to this. See example below:

=VLOOKUP(A183,LPRawData!$A$2:$C$625,2,FALSE)-B183

That formula is found in the sheet BearRawData and is looking for information contain in LPRawData (column A is where the alpha or numeric or alphanumeric information is contained).

I am getting a lot of #N/A back as results (only those with values or numbers are returning matches and results).
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-11-04 15:33, fmaresca wrote:
Actually I did something similar to this. See example below:

=VLOOKUP(A183,LPRawData!$A$2:$C$625,2,FALSE)-B183

That formula is found in the sheet BearRawData and is looking for information contain in LPRawData (column A is where the alpha or numeric or alphanumeric information is contained).

I am getting a lot of #N/A back as results (only those with values or numbers are returning matches and results).

What is in A183 -- a number or an alphanumeric value like AX6Y?

And, What kind of values do you have in column 2 of LPRawData!$A$2:$C$625?
 

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
Column A has Alphanumeric information (as an example P4949W165)
Column B has Quantities (as an example 1000)
Column C has a per share amount (as an example 23.45).

From A2 to C625 would have what I indicated (in columns A through c) for all 624 stocks and bonds.
This message was edited by fmaresca on 2002-11-04 15:48
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-11-04 15:42, fmaresca wrote:
Column A has Alphanumeric information (as an example P4949W165)
Column B has Quantities (as an example 1000)
Column C has a per share amount (as an example 23.45).

From A2 to C625 would have what I indicated (in columns A through c) for all 624 stocks and bonds.
This message was edited by fmaresca on 2002-11-04 15:48

And you're substracting a value in B from a quantity retrieved for the value in A that is associated with the corresponding value in B -- that is, what your formula says. If so, you have the problem that, for some values in A, you get #N/A which thwarts substraction. Are you trying to do the substraction if only if VLOOKUP returns a quantity and otherwise do/return something else?
 

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
Not quite sure if I understand your question, but here's the logic I am trying to apply. I am doing a VLOOKUP based on Column A in each sheet. Once I find a match (since both sheets are not arranged in the same order) it goes to the next column (Column B) which finds the quantity (a number/value), that should be subtracted from Column B in the other sheet to arrive at the difference (to reconcile). Since what is in Column B in both sheets are numbers/values, the result should be a value and not #N/A.

Thanks.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-11-04 16:24, fmaresca wrote:
Not quite sure if I understand your question, but here's the logic I am trying to apply. I am doing a VLOOKUP based on Column A in each sheet. Once I find a match (since both sheets are not arranged in the same order) it goes to the next column (Column B) which finds the quantity (a number/value), that should be subtracted from Column B in the other sheet to arrive at the difference (to reconcile). Since what is in Column B in both sheets are numbers/values, the result should be a value and not #N/A.

Thanks.

If this formula

=VLOOKUP(A183,LPRawData!$A$2:$C$625,2,FALSE)-B183

should return a numeric value from column 2 of LPRawData!$A$2:$C$625 for the value in A183 and it fails to do so (that's, you get #N/A), that means the value in A183 is not in LPRawData!$A$2:$A$625. It could be the case that either the value in A183 or the one in LPRawData!$A$2:$A$625 have extraneous characters such spaces around them, that is:

"XYZ3U" is not the same as "XYZ3U ".

Check the length of the lookup value and the one in the lookup table by using LEN...

=LEN(A183)=LEN(LPRawData!x)

where x is the cell the same value as in A183 appears. They should be equal in length.
 

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
Thank you, that is the problem. Which leads me to a related question to that problem. Without going into all 624 cells in Column A to manually fix them, how can I remove these extraneous spaces automatically?
 

Forum statistics

Threads
1,144,441
Messages
5,724,378
Members
422,547
Latest member
Vision1291

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