"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

excel table

Excel VLOOKUP review

by

I was introduced to Excel by accident. I started a job and got a PC with Excel on it. The first tasks involved data entry and lists maintenance.

As my assignments evolved, I needed to do some basic queries…

That was the time when the VLOOKUP function made me  feel the power of Excel.

Here are the pros why you should implement a VLOOKUP function

  1. It searches a value in the backend left index column and retrieves the specified attribute.
  2. It is easy to learn and write.
  3. It does not require any complicated DB relational stuff.
  4. It updates automatically.
  5. It can use wild cards.

After the initial feel, I found some shortcomings worth talking about…

Here are the cons why you should keep away of the VLOOKUP function

  1. Only searches a value based on a single condition.
  2. Only searches a value in the left index column (you keep moving columns in the backend list to implement the formula successfully).
  3. You can not know the location of the returned value (in terms of row or cell reference).
  4. It only retrieves the first instance.
  5. It is affected by a non sorted ascending index column.
  6. It does not allow you to search a value based on multiple criteria.

I still use the VLOOKUP formula because it is very easy to write; however, I cannot cope with the limitation of not being able to search multiple criteria, and the limitation to search only the leftmost column. I feel bored by moving columns to the left and trying to find out the solution to a multiple criteria lookup.

In consequence…

I now use the INDEX and MATCH formula. It appears complex at first, but once you get it, you can overcome those well-known VLOOKUP limitations and even get more versatility.

What do you think of the VLOOKUP gem?

How to Convert a Data Table to a Range

by

A reader imported some data from an external source and ended up with a table. They asked how they could get a copy of the data itself into the spreadsheet, without the table.

What is going on here?

When Excel 2007 imports data from an external source that can be updated back, such as the data connection, database (like SQL Server), query (via MS Query), and so on, it puts the data into a ‘Table’ object instead of just putting the contents right into your spreadsheet. Any change in the data is updated back to the data source if possible, and the data is updated when the source is changed.

This is useful behavior … apart from when you want a copy of the information, not a table! The user that asked the question wanted the data as a regular Excel worksheet data instead so it could be edited and manipulated without impacting the original data source.

Importing Data

If you want to import some data, first, we can look up our friend, Microsoft Query. Select the Data ribbon, click the From Other Sources button, and select your external data source.

After selecting the source, we need to specify where the data will be put. Note that there’s no option for just putting it as data, it has to be a ‘Table’, pivot table, or a pivot chart and report:

Now we’ve got the data imported as a table object into Excel, as seen here. Note that when a cell inside this table object is selected, the ribbon displays ‘Table Tools’:

Converting to a Range

Now for the actual solution we wanted, that is converting the data so it will become a part of the worksheet.

To do that, we’ll use the ‘Convert to Range’ button in the ribbon:

Pressing it, we’ll have to OK the change. Note that it’s not reversible, that is, you can’t undo it:

And now the data is a part of the worksheet instead of in the table object; note the ribbon:

Finishing Touches

One thing remains, the data still looks like a part of the table object and it does not match the rest of the sheet. To fix this, we’ll use the Format Painter. First select a cell outside of the (former) table area, so we can copy its format, then click on the Format Painter button:

Now, select the old table area:

And voila! Fully editable contents in your Excel worksheet.

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software.

For more Excel tips from Yoav, join him on Twitter or Facebook.

Disclaimer:

*This site and the products and services offered on this site are not associated, affiliated, endorsed, or sponsored by Microsoft, nor have they been reviewed, tested or certified by Microsoft.

**All product and company names are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.

Products & Services

Free courses

Company

© 2009-2019 ExcelGuruBlueprints.com. All rights reserved.