Increasing your virtual memory (paging file) with Windows XP


In many operating systems you can use free disk space (which is typically in a abundance) to emulate RAM. It’s obviously not as beneficial as physically adding more RAM to your machine, however here is a brief guide to customising your virtual memory configuration using Windows XP.
- Press the Windows Key + Pause|Break key to open the System Properties windows (there are many ways to do this!)

System Properties

System Properties


- Click on the Advanced tab.
- Click on the Settings button (under the Performance section).

System Properties - Advanced Tab

System Properties - Advanced Tab


- Click on the Advanced tab.
Performance Options

Performance Options


- Under the Virtual memory section, click on the Change button.
You might like to change the Processor scheduling and/or Memory usage to suit your needs.

Performance Options - Advanced Tab

Performance Options - Advanced Tab


- Select the Custom size radio button.
A good rule of thumb (well, my rule of thumb) is to set the Initial size (MB) to one-and-a-half times (1.5x) your actual RAM, the Maximum size (MB) to twice (2x.) This can be determined from the initial System Properties section.

In my case, I have 3.49Gb of reported RAM. Assuming 1024Mb == 1Gb then this is equivalent to approx. 3574Mb. Multiplying this by one-and-a-half (1.5x) equals 5361Mb (which is approx. equal to the Windows recommended 5359Mb.) Similarly, twice (2x) this value is 7148Mb (which is approx. equal to the used value of 3.5Gb x 1024Mb x 2 = 7168Mb).
- Click the Set button.
- Click the OK button.

Virtual Memory - Primary (C:\) Drive

Virtual Memory - Primary (C:) Drive

You can set the swap or virtual memory paging file on as many drives as you like – perhaps one that is not written to often in case this results in resource competition. This is an assumption I’ve made, so may not actually make a difference.

Virtual Memory - Secondary (D:\) Drive

Virtual Memory - Secondary (D:) Drive


- Continue clicking the OK button until you have exited all windows.
You will be notified that the changes will not take affect until you restart your computer.

System Control Panel Applet

System Control Panel Applet

You can restart now, or defer restarting until later.

Systems Settings Change

Systems Settings Change

I believe that there are physical RAM limits (4Gb?) with 32-bit Operating Systems like Windows XP, however admit that I don’t know enough about the mechanics of this to be certain how/if it applies here. It might be pointless having a 7Gb paging file (let alone two!) as described above. Comments clarifying this are more than welcome.


Feel free to add any comments. If you require further clarification on any part of this how-to guide, please leave a comment, or you can email me at: ryan@kirgs.com or simply visit my website: http://businessanalyst.kirgs.com/ and complete the feedback form on the “contact” section.

Ryan Kirgan is a Business Analyst who operates out of Sydney, Australia.

Business Analyst: Ryan Kirgan

Business Analyst: Ryan Kirgan

  • Share/Bookmark

Viigo has installed itself! Or did it? Optus are at it again.

Looking at the Application icons on my Blackberry I’ve noticed something that wasn’t there before: Viigo RSS Reader. Having no idea what this was (and knowing that I hadn’t installed it myself) I did a quick google search to discover this is an extremely popular RSS reader (as the name suggests) for Blackberry devices.

I didn’t ask for it, so no matter how good it is isn’t really the point.  The problem quickly became that I couldn’t seem to uninstall it! Options->Advanced Options->Applications didn’t contain anything that even slightly resembled Viigo RSS Reader.  Similarly, pressing the Menu Key and selecting Modules in this section does not show anything.

I then delved into the Service Book (also found in Options->Advanced Options) to find that Viigo VPL [Browser Config] was present and active.  Viewing the properties of this service book showed:

Description: Viigo Virtual preload for Optus AU

Pressing the Menu Key and Delete seems to have solved the problem, but with carriers such as Optus being able to deploy potentially unwanted software, who knows for how long it will remain off my phone.


Feel free to add any comments. If you require further clarification on any part of this article, please leave a comment, or you can email me at: ryan@kirgs.com or simply visit my website: http://businessanalyst.kirgs.com/ and complete the feedback form on the “contact” section. Ryan Kirgan is a Business Analyst who operates out of Sydney, Australia.

Business Analyst: Ryan Kirgan

Business Analyst: Ryan Kirgan

  • Share/Bookmark

A comprehensive how-to set up a wireless network (securely) with Windows XP guide

I wrote this how-to guide a while back, however thought I would publish it on my blog in response to: Stop Internet Poachers from Stealing Your Wi-Fi by Rick Broida. My advice to people is diametrically opposed to Rick’s, so hopefully between the two there’s a solution that best meets your requirements :)

Disclaimer: This is how I set up wireless networks, however I am sure that there are a lot of different ways to do this.  Apart from any references to upgrading firmware (in short, don’t do it unless you have to!) there shouldn’t be anything in here that breaks your equipment and ends up costing you lots of money!

In the examples provided I have used:

- Intel(®) PRO/Wireless 3945ABG Network Connection wireless adapter
- Linksys WRT54G wireless router

This document is aimed at enabling secure, wireless access to a wired network that is already operational.  While setting up a home network from scratch is beyond the scope of this document, I will say briefly that there are two main ways (that I know of) to get things working: either the modem does the authentication/DHCP etc. or the router does (and the modem is set to “full bridge” mode.)  The following instructions should work irrespective of the method adopted.

First we need to gather some information about your network…

There are countless combinations of ISPs/modems/routers/wireless cards/PCs that are used when setting up a wireless network, however there are a number of basic principals that are common to all.  This first thing to find out is the IP address of the router (also known in this context as the gateway).  Almost all new routers will have a web interface that lets you configure your network.  Also, keep in mind that it’s a lot easier to configure the router via the wired network than the wireless network (as there is no encryption, a permanent link etc.)

To do this and to find out the IP address of your router…

- Connect your computer to one of the wired ports on the router (i.e. not the uplink port) using a UTP cable (usually blue with a clear 8-pin RJ-45 connector on the end – kind of like a telephone connector but bigger.)  Most home routers have 4 or 5 of these ports.
- Click on the Start Menu.
- Click on Run….
- Type cmd (or command for pre-Windows 2000 machines) and click OK.

Enter "cmd" in the "run" dialog box

Enter "cmd" in the "run" dialog box

The command prompt should open.

Command Prompt

Command Prompt

- Enter ipconfig and press the Enter key.

ipconfig

ipconfig

You’ll notice that there are three Ethernet adapters listed above (you may have more or less).  These correspond to the number of ethernet  network interfaces on the computer; here, two are for wired networks and one is for wireless networks.

As we are connected using the wired Local Area Connection adapter, we need to look at the information listed for that particular device. The default gateway (here, 192.168.1.1) is the IP address of the router.

- Record this number.

Now…

- Open your web browser (e.g. Internet Explorer, Mozilla Firefox, Safari, Opera)
- Enter in the address bar http:// followed by the IP address obtained earlier.  Using the previous example you would enter http://192.168.1.1

Access the router's web interface

Access the router's web interface

This is where things become different for different arrangements.  You will (hopefully) most likely be prompted for a username and password.  This will usually be admin and admin or admin and no password at all.  Sometimes you are solely prompted for a password (maybe simply try password?)

Router Credentials

Router Credentials

The default password will almost always come with any documentation that came with your router – otherwise, you’re router’s vendor will most certainly have it (so try their website or Google search your router model.)  If the password has been set to something that you don’t know and can’t find out, you will have to reset the router (which is usually done by pressing a reset button on the router for around 10 seconds, however be warned that this will restore the factory default settings – except the firmware – and you will lose any changes you have previously made to your router.)

Once connected, you can use the web interface to configure your router for wireless access.

The first thing that you need to do is ensure that the Wireless radio (or similar) option is enabled.  Once this has been confirmed, you need to decide upon, set and record the SSID (think of this as the name of your wireless access point).  At home, mine is called rabbit.  This is arbitrary, but you will need to enter this name when you set up the computers/printers for use in your wireless network (hereafter known as devices) so make it something you can remember.

**DON’T MAKE IT ANY OF YOUR PASSWORDS!**

Also, for reasons outlined later, it is good to set this to something other than the default factory setting (e.g. NETGEAR or WRT54G).

Now for the important information…

The four main components to wireless network security that I find most important (in order) are:

1. Using WPA (or greater) encryption
2. Changing the router’s admin password
3. Enabling access control
4. Not broadcasting the SSID

The first of these is an absolute must – the second and third strongly recommended preferable and the fourth, nice if you have time provided you can sustain good connectivity with its implementation :)

Using WPA (or greater) encryption

I would suggest using WPA (as the mode of encryption (or WPA2 if your wireless card and router support it.)   It is based on an algorithm (AES) that has not been broken (yet or as least not as far as I know!)

DO NOT use WEP.  WEP is based on a weaker algorithm and if enough packets are collected can be broken.

Anecdotal: I have been told that there were competitions for breaking WEP encryption schemes that resulted in the password being discovered within 6 minutes!

DO NOT use nothing!  (This kind of goes without saying :) – or does it? See: Stop Internet Poachers from Stealing Your Wi-Fi by Rick Broida)

If your wireless card or router don’t support WPA (although if it was bought in the last 5 years it should!) you may have to upgrade the firmware of the device in question.  This is like a software update for your hardware, but can be extremely dangerous and render your equipment useless.  If your equipment is this old, you’re probably better off buying new equipment, although be absolutely sure that your equipment doesn’t permit this before spending money needlessly.  For legal reasons I am not going to write any instructions on upgrading firmware :P   Also, it should be noted that anything before Windows XP with Service Pack 2 may have limited WPA capabilities.  To check if you have Service Pack 2 (or above) installed…

- Right-click on My Computer.  This may be on your Desktop or in the Start Menu.
- Click on Properties.

Windows System Properties

Windows System Properties

The information should be listed under System: under the General tab.

Back to the web interface of the router…

When using WPA you want to look for something on the web interface of the router that says WPA-PSK, WPA shared key or WPA passphrase.

Once you set this passphrase, record it as this will be what you use to set up the devices on your network.  I generally get Windows to handle my wireless connections.  Using Windows to do something is probably a first, but from experience the software utilities that come with most network cards seem to provide intermittent connectivity.

Once this information has been set, we need to configure the device.  The instructions below are for setting up a Windows XP machine (with Service Pack 2).

- Click on the Start Menu.
- Click on the Control Panel.

Control Panel in the Start Menu

Control Panel in the Start Menu

- Double-click on Network Connections.  If you can’t see this icon, click on the Switch to Classic View link in the top-left hand corner under Control Panel.

Network Connections in the Control Panel

Network Connections in the Control Panel

Switch to Classic View

Switch to Classic View

Network Connections Control Panel

Network Connections Control Panel

- Right-click on the Wireless Network Connection icon and click on Properties.

Wireless Network Properties

Wireless Network Properties

- Click on the Wireless Networks tab.
- Ensure that the Use Windows to configure my wireless network settings checkbox is checked.
- Under Preferred networks click on the Add… button.

Wireless Networks Tab

Wireless Networks Tab

- Enter the SSID set when configuring the router.
- Change the Network Authentication to WPA-PSK.
- Change the Data encryption to TKIP.
- Enter your WPA passphrase as the Network key.
- Confirm network key.

Add Wireless Network

Add Wireless Network

- Click the OK button to confirm.

You should now be connected via your wireless adapter.

Wireless Network is now connected

Wireless Network is now connected

Wireless Connected (mouseover)

Wireless Connected (mouseover)

You may need to restart the machine to get the computer to connect to your router.  Alternatively, you may need to repair your wireless connection…

Repairing the connection

It has been my experience that sometimes the wireless connection won’t be active immediately, so repairing the connection is a way to give things a bit of a kick-start without having to restart.

Wireless Connection Not Connected

Wireless Connection Not Connected

- Right-click on the wireless icon (with the three (3) yellow waves emanating out) in the system tray.
- Left-click on Repair.

Repair broken Wireless Connection

Repair broken Wireless Connection

This will initiate Windows running through the “wireless repair” motions.  You should see dialog boxes indicating that the network adapter is being disabled, then enabled.  This is followed by the adapter connecting to the wireless network, the wireless network adapters ip address being renewed, the NetBT being cleared and refreshed and the same for the DNS cache.

Example:

Enabling your Wireless Adapter

Enabling your Wireless Adapter

Refreshing NetBT

Refreshing NetBT

Hopefully you’ll now see our happy friend, the Windows Network Connection is now connected speech bubble.

Wireless Network is now connected

Wireless Network is now connected

Still not connected?!?

There could be many reasons why this hasn’t worked.  Some common problems:

  • The adapter is disabled.  With laptops, there is often a physical switch located that disables (usu. Bluetooth and) wireless network devices.  Ensure that this is switched on (mine has a light that turns blue when on, orange when off.)  These switches can also be controlled using function keys.  Look for icons (also, usually blue) above some of your function keys.  It will generally look like the wireless icon (with the “radio waves”) in the system tray.  Hold down the “function” key on your keyboard and press this button – continually pressing this button should cycle through the different adapter enabled/disabled modes.  The adapter may also be disabled under Windows.

(I’ll update this list as best I can)

Changing the router’s admin password

Almost every router has this in a different place in it web interface, but in all of the router’s I’ve configured it’s in there somewhere.  You may need to enter the old password (as mentioned above, usually admin or nothing at all!) beforehand.  Record this, or you won’t be able to make changes to your router without having to resort to reset the router and starting all over again.  This is the username and password that you will use to connect to your router via the web interface.

Enabling access control

Each network adapter (wireless and wired) has a unique hardware address.  You might have heard of this as a MAC address or physical address.  We want to limit wireless network access to only those devices you own.  To find this address using Windows…

- Click on the Start Menu.
- Click on Run….
- Type cmd and click OK.

Enter "cmd" in the "run" dialog box

Enter "cmd" in the "run" dialog box

The command prompt should open.

- Enter ipconfig/all and press the Enter key.

Wireless Adapter MAC address

Wireless Adapter MAC address

- Under Ethernet adapter Wireless Network Connection: look for the value of the Physical Address.

This is the MAC or Hardware address mentioned earlier.  The address needs to be entered in to an Access List or Access Control List of permitted  MAC addresses using the web interface for the router.  Another phrase to look for is MAC address filtering or Hardware address filtering.

In this example, the address is 00-18-DE-20-C2-E7.  The format that your router requires may be different, although this is usually given.  You may need to separate the address pairs by using colons (:) (e.g. 00:18:DE:20:C2:E7) or nothing at all (e.g. 0018DE20C2E7).  This should be case insensitive, but some routers might be more picky than others :)

Not broadcasting the SSID

Generally there is an option using the web interface of the router to disable the broadcast of the SSID.  The idea behind this is that if people can’t find your router then they won’t be able to connect to it.  This is also why I mentioned earlier that changing the SSID from the default is a good idea.  Despite this, pretty much all modern network cards will be able to connect to a router without the SSID and furthermore, if someone is looking to hack your network then the notion that “security by obscurity” not working rings true.  Another upshot of this is that occasionally your network card might not be very good at remembering what wireless access point it should be connecting to and you will experience intermittent connectivity.

Anecdotal: In the last dozen or so wireless networks that I’ve set up there have been a number of physical impediments between the wireless adapters and the router.  All of these were initially set to use the wireless utility that came with the network card (c.f. letting Windows handle wireless networking) and also, were on networks where the SSID was not broadcast.  Removing the vendor’s wireless utility, letting Windows handle the wireless network and setting the SSID to broadcast solved the problem in every instance.

Do not worry too much about any security risks that might be associated with broadcasting the SSID – even if a person can “see” your wireless access point, they will still need the WPA passphrase and their wireless card’s MAC address won’t be in your access control list.

More often than not, after making changes such as this you will be required to reboot or restart the router.  Note that this is different to resetting the router.

Final words

I’ll finish by saying that I don’t think security by obscurity works (to use a catchphrase of doyen, mentor and networking guru, John Dodson.)  Be it for closed-source software (did someone say Microsoft?) or by hiding your wireless network’s SSID.  This is not to say that using Linux and OpenOffice will save you from security breaches, nor will there never be an occurence that broadcasting your SSID was solely responsible for someone stealing your Wi-Fi.  It’s all about probability and taking sensible measures.  It’s also about acceptance that if you couldn’t be bothered taking measures then you should live with the consequences.

MAC address spoofing will bypass any MAC address filtering measures and I’m sure there are encryption specialists who will talk down the imperviousness of WPA (sequential IVs?  Or is that WEP?)  For my mind, it’s all about maintaining the balance between what is easy to implement for your average user, yet secure enough that people won’t connect to your wireless network (sometimes even unintentionally via connect-automatically-to-wireless-networks-in-range-happy Windows!)

Fingers crossed after all of this you’re in business :) and a little bit more secure.


Feel free to add any comments. If you require further clarification on any part of this how-to guide, please leave a comment, or you can email me at: ryan@kirgs.com or simply visit my website: http://businessanalyst.kirgs.com/ and complete the feedback form on the “contact” section. Ryan Kirgan is a Business Analyst who operates out of Sydney, Australia.

Business Analyst: Ryan Kirgan

Business Analyst: Ryan Kirgan

  • Share/Bookmark

Google Earth Update Review [v5.0.11337.1968 (beta)]

The other day I updated my Google Earth installation to 5.0.11337.1968 (beta) and was happy to see some new functionality:

Flight Simulator

Fly a F16 (a.k.a. “Viper”) or old-school  SR22 over your house.  As far as I know, this function was previously available to Google Earth Pro users only.  Not advisable for those who already have a fear of flying as you realise just how b****y hard it is to fly a plane :)

You can do this by clicking on Tools -> Enter Flight Simulator…

Record a Tour

This function is really cool.  It basically captures and records your movements in Google Earth, i.e. what you see on your screen can be saved and replayed.

The file size is small because it is the movements (rather than the actual images) that are captured.  It is for this reason that the file size is contingent on the number of events rather than the duration of the tour.  I recorded a 30-second tour with 5 or 6 events resulting in a 4kB (tiny) file.

The tour can be saved as a KML (unencrypted) or KMZ (encrypted) file and emailed to others to open with Google Earth. It also permits audio recordings, however, this will no doubt add to the file size (with audio being continuous data rather than the discrete events mentioned previously.)

Historical Imagery

This doesn’t seem to be implemented yet, however, it looks as though you can use a slider to look at the aerial shots over time.  It didn’t work at all when I started writing this blog entry, but now (some 5 minutes later) it is allowing me to select dates between July, 2001 and today.  The fact that it is killing Google Earth every time I move the slider should be ignored as I’m sure those clever ducks at Google are on the case :)   (Might be why it is “beta”?)

Google Earth Historical Imagery slider

Google Earth Historical Imagery slider

I just tried it again and it works, however, you need to be patient.  The aforementioned crash *might* have been due to me violently sliding the slider up and down.  Also, there are limited samples (only 4 between Feb 2006 and now, Feb 2009, for a 1255ft view of the Sydney Harbour Bridge; only 1 over my house.)

Bouncing icons

Place your mouse over a placemark icon and it bounces.  Very exciting.

New Splash Page

A different Google Earth image is shown on start up.  All I can say is: “Wow.”

Ocean

Go under the sea (apparently).  I entered in “Great Barrier Reef” as a location, however couldn’t see any streetview-camera-like icons.  I’m sure this is a great feature for those who are interested in that kind of thing.

Mars!!!

Find your prospective plot of land on Mars for 2032.  Street-view doesn’t work either which was a bit disappointing.  What are we paying Google for? (oh, that’s right, we’re not :) )

My existing KML files loaded when I changed from Earth to Mars, so it looked a bit strange to have the overlay on the red planet.

Customers & Zones on Mars(?!?)

Customers & Zones on Mars(?!?)

COLLADA models

I’m not too sure what these are, but it appears that you can link to *.dae files. A quick Google search suggests this is some kind of 3d modelling format.

All in all, great work, Google Earth.

You can download both the Free and Pro versions of Google Earth from: http://earth.google.com/


Feel free to add any comments. If you require further clarification on any part of this review, please leave a comment, or you can email me at: ryan@kirgs.com or simply visit my website: http://businessanalyst.kirgs.com/ and complete the feedback form on the “contact” section. Ryan Kirgan is a Business Analyst who operates out of Sydney, Australia.

Business Analyst: Ryan Kirgan

Business Analyst: Ryan Kirgan

  • Share/Bookmark

The weighing twelve (12) balls riddle – which one is a different weight?

This is a riddle that a friend (Carl “Chunky” Benson) put to me a few years back. A few A4 pages of scribblings and I had accounted for an “answer” for almost all possible outcomes (almost), however, I never arrived at a complete solution.

There’s every chance that I’m just b****y hopeless at Maths, so prove me wrong people and make me look like a dill :)

Premise

  1. You have twelve (12) balls and a set of balance scales.
  2. One (1) of the balls is a different weight to the other eleven (11) balls.
  3. You are allowed to use the balance scales three (3) times.
  4. You need to determine which ball is the “odd one out” and whether it is heavier or lighter than the other balls.

Now these are balance scales I’m talking about.  While a solution involving electronic scales would be effective, I don’t think it’s what the riddle’s architect had in mind ;)

Balance Scales

Balance Scales

I’ve seen a few similar riddles such as this posted elsewhere on the Internet, however, several state that the ball is either heavier or lighter; a far easier problem to solve. Maybe it should be called something different than “weighing twelve…” as your solution mightn’t necessarily involve weighing all the balls.

Good luck (and no posts to other sites please!)

Feel free to add any comments. If you require further clarification on any part of this riddle, please leave a comment, or you can email me at: ryan@kirgs.com or simply visit my website: http://businessanalyst.kirgs.com/ and complete the feedback form on the “contact” section. Ryan Kirgan is a Business Analyst who operates out of Sydney, Australia.

Business Analyst: Ryan Kirgan

Business Analyst: Ryan Kirgan

P.S – Any hate mail you want to write (after obsessing over this problem) can be sent to carl@mytrade.com.au :)

  • Share/Bookmark

Matching data in a table in Excel® using INDEX and MATCH (- a VLOOKUP alternative)

This article describes an extremely basic Excel® function (well, two functions actually) and is more a test of my new Wordpress installation than anything else :)

It describes how you can use the INDEX() and MATCH() functions to look up data in a matrix.

Background to VLOOKUP()

Many users are familar with the VLOOKUP() (vertical lookup) function that simply looks up a value in the leading column of a table and returns the value in another specified column of the same table.  The required parameters are:

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup) where Range_lookup is optional.

For example:

With the data in the table below…

A B
1 Fruit Colour
2 Apple Red
3 Banana Yellow
4 Orange Orange (what else!)

…the formula

=VLOOKUP(“Banana”,$A$2:$B$4,2)

Result: Yellow

will search for “Banana” (1st parameter) in the leftmost column of the table ($A$2:$B$4) (2nd parameter) and return the value that is in the 2nd (3rd-parameter-th) column.

It should be noted that the 1st parameter (the value of which, here, is “Banana“) can also be a cell reference (e.g. $D$1) rather than statically entered into the result cell formula.

Problems with VLOOKUP()

I’ve never like this function for several reasons:

  1. Any changes to the structure of the table (such as the insertion of a new column) may result in the incorrect field value for that row entry being returned.
  2. You need to determine what column number the information you want return resides in.  (This is obviously fine for small tables, but you shouldn’t have to waste time determining this in the first place!)
  3. The table needs to be (ascendingly?) sorted by the first column to work correctly.
  4. It takes longer than it ought to replicate the query to return the value for a different column.

So what are our alternatives?  I prefer to use the INDEX() and MATCH() functions.  For my mind this approach is not only more flexible, but it’s also more intuitive.

The premise is much the same, however, instead of matching only the leftmost column, you can also match a row and column to determine the coordinates of the value you want returned.  It’s effectively a combination of the VLOOKUP() (vertical lookup) and HLOOKUP()* (horizontal lookup) functions.

* HLOOKUP() simply matches a value to the first row (cf. column) of the table, returning the value in a corresponding column (cf. row)

Using INDEX() and MATCH()

I’ll describe the INDEX() and MATCH() functions separately.  There are two permutations of the INDEX() function: array and reference.   For the purposes of this post, I’ll use array.  The INDEX() function is described as:

=INDEX(array,row_num,column_num)

For example:

Using the same data from the previous example, we designate the array parameter as the table that contains the data we wish to return.

A B
1 Fruit Colour
2 Apple Red
3 Banana Yellow
4 Orange Orange (what else!)

To retrieve the same result from the first example, we can use the formula:

=INDEX($A$2:$B$4,2,2)

Result: Yellow

This returns the value from the array ($A$2:$B$4 – 1st parameter) that is in the 2nd row (2nd parameter) and the 2nd column (3rd parameter).  Using the function in this way INDEX() is certainly no better than using VLOOKUP() (in fact, it’s worse, because there is no dynamic functionality – that is, there is no “searching” involved.)

So, how do we dynamically determine the row and column numbers based on certain criteria?  We can use the MATCH() function, which can be described as:

=MATCH(Lookup_value,Lookup_array,Match_type)

To determine the row we are interested in (here, row 2 – the “Banana” row) we can use the formula:

=MATCH(Banana“,$A$2:$A$4,0)

This searches the data array ($A$2:$A$4 – the 2nd parameter) for the lookup value (”Banana” – the 1st parameter) and ensures that it matches exactly (Match_type – the 3rd parameter – equals 0; returns #N/A if there is no match.)

Result: 2

Similarly, we use this function to determine our required column number (here, column 2 – the “Colour” column) we can use:

=MATCH(Colour“,$A$1:$B$1,0)

This searches the data array ($A$1:$B$1 – the 2nd parameter) for the lookup value (”Colour” – the 1st parameter) and ensures that it matches exactly (Match_type – the 3rd parameter – equals 0; returns #N/A if there is no match.)

Result: 2

It should be easy to recognise from this point that for a much larger table, this takes a lot of the guess work out of determining the appropriate column number.

=INDEX($A$2:$B$4,MATCH(“Banana”,$A$2:$A$4,0),MATCH(“Colour”,$A$1:$B$1,0))

Result: Yellow

Accomodating changes to the table structure

As mentioned, one of the advantages of using the INDEX() and MATCH() functions is its ability to adapt when changes are made to the table structure.

A B C D
1 Fruit Price Colour Qty
2 Apple $1.50 Red 2
3 Banana $2.50 Yellow 7
4 Orange $0.80 Orange (what else!) 8

=INDEX($A$2:$D$4,MATCH(“Banana”,$A$2:$A$4,0),MATCH(“Colour”,$A$1:$D$1,0)) will still return “Yellow” (once columns C and D are accounted for) as the 3rd parameter of the INDEX() function is now 3, while…

=VLOOKUP(“Banana”,$A$2:$D$4,2) will now return $2.50 as this is the value in the 2nd column of the “Banana” row, that is, it hasn’t adapted to the change in table structure.

Using named ranges

I love named ranges (as much as one can love an Excel® function) and using them can simplify even the most complicated formula.  I will describe named ranges in more detail in another article, but for the sake of this piece, simply put, named ranges are names that refer to a cell or cell range.  Using the above example, you could define or “name” the $A$2:$D$4 cell range as “Data“, $A$1:$D$1 as “Fields“, $A$2:$A$4 as “Fruits“, $B$2:$B$4 as “Prices” and so on.  You can name ranges by selecting Insert -> Name -> Define from the Excel® file menu.  Once this has been done, your formula simplifies to:

=INDEX(Data,MATCH(“Banana”,Fruits,0),MATCH(“Colour”,Fields,0))

Result: Yellow

An additional example – the best use of INDEX() and MATCH()

This combination of functions is probably best utilised when using 2-dimensional matrices that have column and row headings.

For example:

A B C D
1 Origin/Destination Sydney Melbourne Brisbane
2 Sydney 0 885 926
3 Melbourne 885 0 1697
4 Brisbane 926 1697 0

With the appropriate named ranges, your formula to determine the distance between two given points could be:

=INDEX(Distances,MATCH(“Brisbane”,Origins,0),MATCH(“Sydney”,Destinations,0))

Results: 926

Other points

  • As mentioned with the VLOOKUP() example, “Banana” and “Colour” could both be cell references (you could even use a drop-down menu!)
  • You could use VLOOKUP() and MATCH() (e.g. VLOOKUP(“Banana”,$A$2:$B$4,MATCH(“Colour”,$A$1:$B$1,0)) but you are still limited to your “row” search term being in the first row and that the list is sorted by the first row.
  • Complications can arise if there are not unique values in a column or row (e.g. “Apples” can be “Red” or “Green“, so could have two entries in the table.)  There are ways in which you can handle this (sometimes using SMALL() and BIG() that are beyond the scope of this article.)

Othewise, I’ve found this to be a good alternative to the VLOOKUP() function; one that’s easier to use and more powerful.

There are certainly aspects of the functions that I have omitted.  I’d welcome any comments or criticism, so please feel free to add any comments.  If you require further clarification on any part of this article, please leave a comment, or you can email me at: ryan@kirgs.com or simply visit my website: http://businessanalyst.kirgs.com/ and complete the feedback form on the “contact” section.

Ryan Kirgan is a Business Analyst who operates out of Sydney, Australia.

Business Analyst: Ryan Kirgan

Business Analyst: Ryan Kirgan

  • Share/Bookmark