VLOOKUP on Alphanumeric

[fmaresca]

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.

 

[dtaylor]

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]

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]

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]

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]

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]

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]

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]

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]

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?