Tom Moholland
asked on
Formula seems to be acting different than others
Problem with formula Please take a look at the results sheet RB row 111 the formula as I see it should be displaying 196,213.95 in cell C111 and 32,454.10 D111, but for whatever reason it seems to acting differently for this row then all the other results with the same formulas and I can't understand why
test.xlsx
test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If Saqib's formula does not work......
What is the difference between the two?
@Saqib. Sorry, I was originally working with Tom on this. Our formulas are virtually the same, except you used "0" and I used "False". Both give exact matches without the need for the data to be in ascending order.
In addition to the problem noted by Saqib and Flyster, I am also concerned about the column header labels in worksheet GL INCOME row 4 and worksheet RB row 1. These cells contain numbers with in format 000. Some of them are in Text format, while others are in General, and very few of the values start with a single quote to designate text.
If I click on one of the cells that uses General number format and has no single quote, then hit Enter, it will change its value. Specifically, it will be converted from text (and leading zeros) to a number with no leading zeros. As a result, lookup formulas relying on that column will break instantly.
Fortunately, the fix is simple. Select GL INCOME row 4 and change the number format to Text. Then select RB row 1 and change the number format to Text.
If I click on one of the cells that uses General number format and has no single quote, then hit Enter, it will change its value. Specifically, it will be converted from text (and leading zeros) to a number with no leading zeros. As a result, lookup formulas relying on that column will break instantly.
Fortunately, the fix is simple. Select GL INCOME row 4 and change the number format to Text. Then select RB row 1 and change the number format to Text.
ASKER
What was wrong with the original formula?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Flyster for you help!
=VLOOKUP($A111,'GL INCOME'!$A$7:$CA$347,MATCH