Monthly Archives: March 2015

Vlookup: Learn it, Love it and leave it Part 3 –False Versus True

Posted on by
Reply

Data is only as good as the preparation!

  • What happens if we do not force Excel to return the exact data we want?
  • What about looking up an item when there may be more than once instance of it?
  • Troubleshooting data—Can you do it when you are accustomed to using the formula building dialogue box?

To help limit errors we can use three helpful keys to avoid reporting incorrect data:

False versus True
Unique Keys
In Cell / Formula Bar editing versus CTRL+A Dialogue editor

We will discuss False versus True in this post. Download and open this Excel file and follow along.

False versus True:

If you need data integrity, always use False in the [Range_Lookup] section of the Vlookup.

Excel’s formula is broken down as such:

vlookup(Lookup_value, table_array,col_index_num, [range_lookup])

[range_lookup] is bracketed meaning it is optional, yet it determines if the result is exact or a close

True: Returns an approximate match = dangerous when reporting data

Blank: Appears to default to TRUE

False: Returns data only if the lookup value exists, which runs the risk of a #N/A as a result, and ONLY finds the first instance of the lookup value

Vlookup True False Blank

9 is the lookup value. Which output would you report to your boss?

 Why would people use True?

A possible reason includes avoidance of the #N/A result which can disrupt sums, counts and other formulas.

Another is when the source is sorted–you can find something close.

Close / Approximate / Incorrect output could  drastically alter sums, counts, etc.—and the end user doesn’t even know. Which is worse? #NA or incorrect data?

Takeaway: Avoid TRUE unless you have a specific case to use it.

Ideal  formula would look like this:

VLOOKUP(A3,C3:D10,2,FALSE)

To avoid the #N/A add in error control using Iferror or other If statement constructs:

=Iferror(value, value_if_error)

            value_if_error setting:

Null version:   =Iferror(vlookup(A3,C3:D10,2,FALSE),””)

Zero Version:  =Iferror(vlookup(A3,C3:D10,2,FALSE),0)

Adding in Error control cleans the output

Adding in error control cleans the output. I used the “” or Null method to generate a blank. Using Zero may imply a different meaning.

Note:  the method chosen should account for what formulas if any may rely on the output—a null is not the same as a zero when determining averages, counts, etc. Refer to the example Excel file to see the consequences of True/Blank versus False and the Zero versus Null on reporting. Always run sample checks for proper averages, counts etc. Know the question you wish to answer to determine Zero or Null in error control.

“IF” constructs (Previous functions work in current versions of Excel):

Pre 2007/10 and forward:

=IF(ISERROR (value))

https://support.office.com/en-AU/article/iserror-function-6a5f3e99-40bc-43ce-a7c8-e79b7b6af5d5

2007/2010 and forward:

=IFERROR(value,value_if_error)

https://support.office.com/en-AU/article/iferror-function-f59bacdc-78bd-4924-91df-a869d0b08cd5

2013:

=IFNA(value, value_if_na)

https://support.office.com/en-au/article/IFNA-function-6626c961-a569-42fc-a49d-79b4951fd461

You are now on your way to sourcing the intended data and not accidentally retrieving unintended data. Look for my next posts on Unique Keys and learning to edit formulas directly in cell or via the formula bar instead of the formula building dialogue box.

Vlookup: Learn it, Love it and Leave it… Part 2 Enhancements

Posted on by
1

In Part 2, I showed you how to add the dynamic aspects to a Vlookup formula, but we left a lot of room for error. In particular the user may enter data that cannot be found in the data. I showed how entering an “a” for item # failed, likewise the user may wish to enter January, not knowing or realizing Jan is the column header. Part 2 Enhancements shows you how to add validation rules to speed reporting by helping the user enter only what is needed.

Tools to be used: Data Validation.

First we want to be sure that the user only selects valid data.

To do this we will look at our two selections we made Item # and Month in cells C16 and D16. Select cell C16 and then go to:

Ribbon Data tab >> Data Validation >> Validation Criteria >> select List

Click on Data Validation to open the contextual/dialogue box for your inputs:

DataValidation

 In the dialogue box navigate to the Allow: box.

Use selection box by activating the drop down and then select List.

DataValidationList

In Source:  enter the limited range (array) that has all of your items.

Data Validation List Source

For this example we will use the data table itself. In more advanced files we would use a control table to limit our possible items from a master list.

We will enter: $A$3:$A$12.

 

 

Once you hit enter, the cell C16 will now show a drop down arrow allowing you to select only what is in the list you referenced.

Data Validated Cell

 

You can do the same for the Months by selecting cell D16 and applying the list limitation with the source set to $C$2:$N$2. You can either manually type this or select the range with your mouse. Use the Practice tab of the linked file to build your own.

 

Congratulations, you have now limited the user entries and also helped ensure they are selecting the right items. You are on your way to building reports.