VLOOKUP query

[PamB]

My VLOOKUP formula was working perfectly - it searched for a value (product code) in worksheet (B) and returned the appropriate price. However, when I copied & pasted the revised product codes and prices into worksheet (B), I now get an #N/A error.

The columns in worksheet (B) haven't changed - just some of the prices and product codes. The product codes are in column A, sorted in ascending order. I have tried changing the format of these cells to Text or to General (they are alphanumeric, e.g. N0220, G2230), but I still get the error. However, when I manually overtype the (same) product code, the formula works... so it's something to do with how Excel sees the format of the product codes/cells.

Any ideas?

Thank you.

[Glenn Kennedy]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[PamB]

Thanks for your reply. Attached are a couple of small samples - one spreadsheet where the formulas work, and one where they don't.

In the spreadsheet where they don't work, the only change is that I have copied & pasted a spreadsheet from a supplier into the Worksheet 'ESS'.

I hope it makes more sense with the samples!

[Glenn Kennedy]

two problems for the price of 1...

Firstly, the range of the lookup should run from A (not from C) in your does not work example.

Secondly, there are loads of tailing spaces after N2010 in the ESS sheet in the "does not work" W/B. These are not present in the "does work" W/B. You will need to get rid of them using CLEAN or TRIM.

[PamB]

Thank you, Glenn. TRIM has solved the problem & got rid of the extra spaces - everything works now! (The range for the lookup did run from A, but I must have inadvertently changed it when I was tinkering with the spreadsheet to attach a sample.)

Thanks again for your help.

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.