John E Pugh - Microsoft Excel Lookup Functions

Microsoft Excel Lookup Functions

I have a significant level of experience with Microsoft Excel, and I chose this example because it demonstrates my knowledge about tables, functions, how to nest functions within each other when required, and my flexibility to choose the right function for the right situation. Excel can be used to accomplish the same task in many different ways, depending on the required outcome of the design.

A common need in an Excel table is to be able to populate parts of the table with data that is stored in another table in the workbook. Below is a table that is missing the first and last names of therapists for each therapy appointment.

Because there is an ID associated with each therapist, and that value is already available in the table, it's possible to use Excel functions to lookup the related name information and pull it into the table automatically.

VLOOKUP

VLOOKUP is used, here, to look for "AS648" in the selected area to the right, and when it's found in H7 it returns the value in the 2nd column of the same row for the selected area, I7: "Shields" to display in E7. This function can be copied to the rest of column E to automatically populate the rest of the last names from the lookup table.

One limitation of VLOOKUP is that the value returned must be to the right of the value being searched for in the lookup array, so in the example from this project it would not be possible to find the last name and return the therapist ID. Also, if a column is later added between the ID and last name in the lookup table, it will break the formulas in Column E, which will not update the return index to accommodate that change.

This first function is called VLOOKUP and it has four parts. It is written in this example as

=VLOOKUP(D7,$H$7:$I$11,2)

D7 is the value we're searching for
H7:I11 is the array of values where we're looking for the value found in D7
2 represents the index number of the column we want returned when we find the value from D7 in the array.

The fourth option for this function, which allows you to tell it to look for an exact match or an approximate match. This defaults to "exact match" and so that can be left out of the function if that's what's needed.

XLOOKUP

XLOOKUP is performing the same task as VLOOKUP was, but the data no longer needs to be formatted left-to-right in order for it to work, and if a column is added between the ID and name, Excel will update the formula to return the value in the Last Name's new positions of J7:J11.

The limitation of this function is that it's only available in the version of Excel included with Office 365, so if you have an older version of Excel installed you will not be able to use it.

This similar function available in the latest version of Excel is called XLOOKUP

It is written in this example as

=XLOOKUP(D7,$H$7:$H$11,$I$7:$I$11)

D7 is the value we're searching for
H7:H11 is the array of values where we're looking for the value found in D7
I7:I11 is the array of values we want returned when we find the value from D7 in the array.

There are other options which allow you to define what happens if the value isn't found, whether the value needs to be an exact match, and in what order (ascending or descending) to search the column.

INDEX MATCH

INDEX and MATCH are used together to return the last name above. MATCH looks for the ID number in column H and returns a number. For AS648 it returns "1" because that value is the first in the selected area of the lookup table. This is embedded within the INDEX function, which has two elements. The first element is the series of cells where you are looking for a return value, and the second is the index number of the needed value. Because the embedded MATCH function is returning a "1" in this case, the INDEX formula returns "Shields" because that's the first item in the selected array.

Although this can be slightly challenging to wrap your head around, it has the same benefits as XLOOKUP and it also works in older versions of Excel.

A combination of two functions can give the same benefits as the XLOOKUP function. Those are INDEX and MATCH.

It is written in this example as

=INDEX($I$7:$I$11,MATCH(D7,$H$7:$H$11,0))

The MATCH function returns an index number that is the index number, or row position, of the matched value. Within the MATCH function:

D7 is the value we're searching for
H7:H11 is the array of values where we're looking for the value found in D7
"0" tells the function to look for an exact match.

The INDEX function returns the value of a cell in an array based on the index number, or row position, provided. Within the INDEX function:

I7:I11 is the array of values where we want to grab and return a single value
The entire "MATCH" function is returning the index number that defines the row position of the value we want returned.

There are other functions which can be used to accomplish the same task and result in the same desired outcome. This is true with many functions in Excel, and having a broad scope of knowledge about those possibilities allows me to select the best function for the task I'm working on, keeping in mind what I'm trying to accomplish, and who the audience is for the sheet I'm building.

For example, it may be tempting to use the easier XLOOKUP function to accomplish this, but if I'm going to deliver the sheet to someone who isn't using Office 365 then I'll use another option instead, so the workbook still functions properly on their computer.

Page updated

Google Sites

Report abuse