Formula seems to be acting different than others

Tom Moholland
Tom Moholland used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

=VLOOKUP($A111,'GL INCOME'!$A$7:$CA$347,MATCH(C$1,'GL INCOME'!4:4,0),0)
Hi Tom,

If Saqib's formula does not work, try this in C111"

Open in new window

If Saqib's formula does not work......

What is the difference between the two?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

@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.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

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.


What was wrong with the original formula?
The original formula was set to an approximate match. As such, the data needed to be in ascending order. The formula will search for the select value until it comes to a value that is less than the one it just checked, then it stops searching. If there are duplicate values, it will give you the last selected value. By adding the "0" (or False) at the end, the formula now looks for an exact match and will search the entire list for the selected value. Once found it will stop searching. If there are duplicate values, you will get the first selected value.


Thanks Flyster for you help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial