Excel 2003 Inconsistent Lookup Between Two Sheets

**lawlore** · 05-10-2011, 05:01 PM

Hi there.

Bit of an odd one, I can't quite put my finger on what's causing this problem, so perhaps someone can shed some light.

I have a workbook with several worksheets, this focussing on the relationship between three of them. The sheets are entitled: "Product List", "Stock Take" and "Summary".

"Product List" contains a heap of manually-entered data about all of the products. I do not believe this is causing the current issue, but include it as it is referred to by the other sheets in question.

---

"Stock Take": This sheet is used for recording stock by location, providing an ultimate total.

Column A- "Product Code" A manually-entered three-letter code referring to an individual stock item (e.g., APT)

Column B- "Product" The full name of a stock item, the name drawn from looking up Column A's code in the Product List. (e.g. "Appletiser")

Column C / D- Empty columns, as some of the products have longer names.

Columns E through K- Various locations in which stock is held (e.g. "Bar", "Kitchen"), with the stock figure manually entered below.

Column L- Empty.

Column M- "Current Stock" - A simple =SUM formula totalling the stock figures across all of the locations.

---

"Summary": This is a locked sheet which draws information from several other sheets, including "Stock Take", to provide an overall summary.

Column A & B- "Product Code" and "Product": identical to "Stock Take".

Columns C through E: Empty columns.

Column F- "Opening Stock": draws its figure from a sheet entitled "Stock In".

Column G- "Total Wastage": draws its figure from a sheet entitled "Stock Usage"

Column H- "Total Sales": draws its figure from the same sheet as column G.

Column I- "Expected Stock": The expected stock level, calculated with =F4-G4-H4 (for example).

Column J- "Actual Stock": Draws its figure from the "Stock Take" sheet, and this is where the problem is arising. The formula for the top line of stock, J3, reads:

Please Login or Register  to view this content.

This reports the correct figure, and does so all the way down to J105. However, below J105, with the same formula, all stock reports a figure of 0, which is not correct. The sum figures on the "Stock Take" page are correct, so there appears to be an issue with the lookup. However, I can't for the life of me understand what it could be: I have checked to ensure the cells have not been formatted or rounded in any way, but all are set as General, and I have attempted to use VLOOKUP but am something of an amateur, and haven't managed to produce a figure in the cell this way, letalone the correct figure.

For rows with no code in their A column, #N/A is correctly reported.

Any help?

**shg** · 05-10-2011, 05:15 PM

Lookup requires that the lookup array (the second argument) be sorted in ascending order.

**lawlore** · 05-10-2011, 05:28 PM

Sorry, I should have specified that; on both sheets, the Product Codes are listed in ascending order, with the first entries being:

AJP, APT, ARC, BAC, BAL

However, this does not necessarily mean the second column, "Product", is alphabetical- the first entry, AJP, looksup a Product of "Prince's 100% Apple Juice". As this second column is not being used in the lookup argument, I don't believe this should have an influence on the outcome?

This Product Code lookup works fine on both sheets, and both are re-sorted in ascending order whenever any more products are added.

**shg** · 05-10-2011, 06:29 PM

Post a workbook and point out the problematic formulas?

**lawlore** · 05-10-2011, 09:08 PM

I shall do tomorrow, should the problem persist, but there's been an interesting development.

I have emailed the workbook to myself from the work account to my home account. At home I confess I am using a different piece of software altogether (OpenOffice Calc)... which has now shown no problem with the worksheets whatsoever, and has given the correct figures everywhere. Hmm.

So, with any luck, I will re-open the same file in Excel 2003 tomorrow and discover that it's cured itself and was just having a mad few moments.

Thanks for your support, I'll let you know what the situation is when I do.

**lawlore** · 05-11-2011, 11:33 AM

I have found the solution, which was not deducable from the information I've given here. For the sake of anyone interested, it was as follows. I will provide screenshots rather than upload the entire workbook.

The problem:

http://i55.tinypic.com/xp62rp.jpg

http://i55.tinypic.com/kegtc4.jpg

J107 and higher providing a reading of 0, rather than the correct answer from the lookup.

The solution:

http://i52.tinypic.com/35m4g1e.jpg

Some long-forgotten about notes on the Stock Take page, which all begin "Phase Out". The lookup was finding this, rather than the correct code, and reporting a 0 figure, as there is nothing listed in their M column. Moving these notes elsewhere has fixed the issue.

Which I suppose leads me to a far simpler question (and one which I realise I could probably find an answer to with F1)- is there a way of making the lookup search for an exact match, rather than an approximation?