Help please - Excel glitch...

**SW1** · 05-21-2011, 11:04 AM

...Or am I losing the plot?!

I’m designing a formula based accounting package in excel but encountering an issue concerning the LOOKUP formula.

There are 44 different types of expenditure, which in this example are listed as "Code1...44". There are also 12 months. Each line of expenditure in each individual month has a totally unique number/code as a result of using the principle of multiplying each months multiplier by the expenditure code;

Month Multiplier
1 = 99
2 = 999
3 = 9999
4 = 99999
5 = 999999
6 = 9999999
7 = 99999999
8 = 999999999
9 = 9999999999
10 = 99999999999
11 = 999999999999
12 = 9999999999999

For example, "Code 1" in month 1 would equal 99 and "Code 44" in month 12 would equal 439,999,999,999,956.

The issue arises in column Q of the attached, where the LOOKUP formula looks for the relevant "Code" based upon the unique number generated in column G. I have checked that all codes are unique (and they must be given the mathematical basis), yet in some circumstances the LOOKUP formula is displaying expenditure detail relating to completely different codes.

I’ve highlighted these in pink. But an example would by "Code 42" in month 1 equalling unique number 4158. Instead of diplaying "Code 42" it displays "Code 4"?

Apologies if the way I've described this is confusing, but I'm not sure how to explain it more concisely. I would be extremely grateful for any ideas as to why this is happening.

Many thanks,

SW1

**Ron Coderre** · 05-21-2011, 11:36 AM

The part you're missing is that the second argument (parameter) of the LOOKUP function must have its values listed in ascending order.

This regular formula is the way that limitation is typically handled:

Please Login or Register  to view this content.

My job is to write financial systems. Unless you are forced to implement the methods in the workbook you posted, the system you're creating seems to be unduly complicated. For instance, instead of multiplying values by a factor between 99 and 9999999999999, I suggest assigning a letter code to each month (A: Jan, B: Feb...etc) and prepending that value to the code.

I attached an edited version of your workbook with a few changes. Oh, who am I kidding, I changed practically all of the formulas. All of the changes are highlighted in yellow.

Your thoughts?

**SW1** · 05-21-2011, 12:17 PM

Hi Ron,

Thank you so much. I'm out and on my BB so won't be able to open until I get home later.

I'll get back to you shortly and yes...I do have a tendency to over complicate!

Daniel aka SW1

**royUK** · 05-22-2011, 03:53 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST