Lookup (array)

Today I want to address a shortcut of sorts. A substitute for VLookup using Vlookups cousin, Lookup. Now just because Lookup is missing the V in the front, doesn't make is any less useful.

Let's see how you can use the Lookup function (array) in place of VLookup. In this example the Lookup formula in cell D3 returns the corresponding item number that is entered in cell E1. Now VLookup can do the same function but requires additional components to make the function work. So Lookup can be easier to use.

	A	B	C	D	E
1	Item	Color			15003
2	51671	Hammer
3	15003	Saw		Result	Function
4	31536	Ladder		Saw	=LOOKUP(E1,A1:B6)
5	53342	Level
6	21367	Pry Bar		Saw	=VLOOKUP(E1,A1:B6,2,FALSE)

Syntax

Lookup value, array

Lookup Value – A value that Lookup searches for in an Array. It can be a number, text, logical value or a name or reference. In the above example my lookup value is 15003.

Array – A range of cells that contain text, number, or logical values that you want to compare with Lookup_value. In the above example my array is Cells A1 – B6.

Basically all you need to know is what you want to find and where you want to find it in. Lookup will find the value and return the corresponding value in the last column in your array.

The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array. 

If an array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first row. 

If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column. 

With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.

BEGINNER, LOOKUP, VLOOKUP, HLOOKUP

Breaking Links To External References

Yesterday a coworker of mine came up to me with an Excel problem he was having. His worksheet was running very slow. When he was navigating it would take 10 to 15 seconds to change tabs. I thought this was odd and went to his desk to investigate.

My first observation was that the size of the workbook was just under 75 meg in size. Not extra large but also not too small. While continuing my investigation I noticed that several tabs had different colors. I inquired about the tabs and he advised that he brought these in from a different workbook.

I took a look at the data in these tabs and many of the cells had vlookup formulas linked to the original workbook.

Well the original workbook is stored on a rather slow server so I asked him if the data in the vlookup fields would need to change when that other workbook was updated and he advised that they did not.

So I decided to break the links.

When you break a link to a source workbook of an external reference, all the external formulas in the source workbook are converted to their current values. I.E. the formula is replaced with just the result of the formula. The calculation is removed. Since there were hundreds of vlookups in his workbook, the performance of the worksheet was impacted.

Now breaking links is serious because once the link is broken it cannot be undone unless you recreate the formula. So if you do decide to break the links, you may want to first make a (just in case) copy of your workbook.

Breaking links in a workbook is as easy as 1,2,3….

1.     On the Data tab select the Connections group and click on Edit links. If the Edit Links command is unavailable (displayed below) then your file does not contain linked information.

2.     In the Source list, click the link that you want to break.

To select multiple linked objects, hold down CTRL, and then click each linked object.

To select all links, press CTRL+A.

3.     Click Break Link.

After breaking the links and saving his workbook, the performance issues were resolved. Now external reference links are not the only cause of sluggish worksheets, but in this instance, breaking the links resolved the issue.

BEGINNER, Break Link, vlookup

Data Validation

Everything we do with Excel starts with data. So today I wanted to examine some cell contents a little closer.

There are times when we look at a cell and what we see is not what really is in the cell.

Take a look at the below example.

Value	Test	Formula
A	FALSE	=ISBLANK(A2)
B	FALSE	=ISBLANK(A3)
1	FALSE	=ISBLANK(A4)
2	FALSE	=ISBLANK(A5)
	FALSE	=ISBLANK(A6)
	TRUE	=ISBLANK(A7)

I want to test the values in my left column (column A) to see if there is a value in the cell or if the cell is empty. I can use the formula =ISBLANK to accomplish this.

As you can see the first 4 rows all have values in them and the formula returns False indicating that. However in the last two cells I get different results. Both cells appear to be empty but in reality only one of them is as indicated by the TRUE result.

Now there are a couple of reasons why a seemingly empty cell would return FALSE for the =isblank function.

First is that there is a value in the cell. Perhaps the font color of the text in the cell matches the background color of the cell giving the appearance of an empty cell.

The second and more common reason is that there is a space value in the cell (someone has hit the space bar and then the enter key. This also can occur when importing values from other data sources such as an Access database.

Take another look at the data. This time I want to do a VLookup for the letter B in my column A and return the value in column B. However my VLookup formula does not work. The reason is again due to a blank space after the B in cell A3. The lookup is searching for “B” and the value in the cell is “B “.

Value	Test	Vlookup	Formula
A	FALSE	#N/A	=VLOOKUP("B",A1:B7,2,FALSE)
B	FALSE
1	FALSE
2	FALSE
	FALSE
	TRUE

This vlookup error is quite common. Almost weekly I am asked to trouble shoot a coworkers worksheet because their vlookup does not work. 9 out of 10 times the problem lies with blank spaces to the right of the value(s) they are using in the look up.

Now you can avoid this common error by using the =Trim function to strip out blanks from both sides of your data.

A third but common occurrence is when a number is represented as a text. Again if we look at the data in column A, I show two numbers (1 and 2). However in this instance, 1 is not a number but a text. The formula ISText fails for the number 2 since 2 is a number in my data.

Value	Blank Test	Vlookup	Formula	Text Value	Formula
A	FALSE	#N/A	=VLOOKUP("B",A1:B7,2,FALSE)	TRUE
B	FALSE			TRUE
1	FALSE			TRUE	=ISTEXT(A4)
2	FALSE			FALSE	=ISTEXT(A5)
	FALSE			TRUE
	TRUE			FALSE

So what does all this mean? Well just because it looks like a duck and quacks like a duck, it does not mean it is a duck. So when you have a formula that you are sure should work. Start by taking a closer look at your data to ensure that what you see is what you really have.