VLOOKUP with text, numbers, and characters in string

[perry_fawn]

I am trying to use VLOOKUP on a string that contains text, numbers, and symbols and keep getting a N/A error. The look-up value is the mixed string of text, characters, and numbers and the return value from the table is numerical. I've tried formating the look-up value as text and still have had no luck. I would appreciate any advice!

Thanks

 

[alansidman]

Would you post some sample data so that we can see specific examples.

 

[perry_fawn]

Sure. In one workbook I have the look-up array which has billing codes in one column and billing descriptions in another column. The codes are numbered 1, 2, 3, ... 29 and the descriptions are 4-4-5 biweekly deduction, semi-annual 12 deductions, semi-annual 24 deductions, quarterly 12 deductions, biweekly billing and deduction,...

These are just a few examples. I have named the entire array BILLDED and I have named the description column BillDescriptions.

In another worksheet I have a cell (say A2) with a data validation list of BillDescriptions. In another cell I have a look-up function: =VLOOKUP(A2, BILLDED, 1, FALSE). Here the products codes are in the first column of the table.

I am recieving a N/A error and I think it is because of the mixed string of data. I'm using Excel 97.

I tried this problem out at home on a newer version of Excel and was able to solve to problem by moving the BillDescription column ahead of the code column but I'm not sure if this will work in 97.

 

[alansidman]

In your lookup table, is the BillDescription in the leftmost column?

 

[perry_fawn]

No it's in the middle of the table

 

[alansidman]

Ok. So that is the issue. Look at this tutorial for syntax.

http://www.techonthenet.com/excel/formulas/vlookup.php

If you dont want to change your table layout, then you will have to use Index/match and you will need to look at this video

http://www.datapigtechnologies.com/flashfiles/matrixlookup.html
Alan

 

[perry_fawn]

Thanks!