Use VLOOKUP to Find the Last Match in a List

VLOOKUP is an awesome Excel function. We all know this. But it certainly has its limitations. One of these is that it can only return the first match from a list.

This is great when looking for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.

Sure we could sort the list so that the last match would become the first, but this is not always an option.

This blog post looks at using the VLOOKUP function to return the last match in a list. The technique and formulas used can be adapted to find the 2nd or 3rd match if required.

VLOOKUP to find last match

Continue reading

Extract the Domain from a List of URL’s

If you are responsible for analysing a list of URL’s in Excel, then you may need to extract the domain from each URL. After extracting the domain you can perform useful reporting tasks such as grouping the domains, or counting the number of unique domains in the list.

This can be quite a difficult task especially if the URL’s are not consistent. In this article we explore a few different approaches to extract the domain from a URL. The method you choose will depend on the appearance of the URL’s in the list and the format you would like to extract the domain in.

This article covers;

  1. Extract the domain including the URL protocol.
  2. Extract the domain ignoring URL protocol.
  3. Extract the domain without the www.
  4. Extract root URL’s that don’t end with a slash (/).
  5. Using helper columns to break up complex formulae.

Continue reading

Pick a Name at Random from a List

In this blog post we look at how to pick a name at random from a list. There are many reasons you may need this kind of random selection from a list.

It may be a neat way of deciding who is next to do the washing up, or to make the tea :). Or it is useful for randomly drawing teams for a knockout cup competition.

To perform this random selection, we are going to use two different functions together. They are INDEX and RANDBETWEEN.

Continue reading

How to Use the TEXT Function in Excel

TEXT Function Examples and Description

The TEXT function is used to convert a numeric value into text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.

Take the example below, where the following formula has been entered into cell C2. The date formula loses its format when concatenated. This is now useless.

=A2&" - "&B2

Date loses formatting when concatenating Continue reading

Excel Timesheet with Different Rates for Shift Work

When creating a timesheet in Excel you may need to calculate different rates of pay. This could be because of overtime, or the type of work being done.

In this tutorial we create a timesheet that uses different rates of pay for working weekends, and also night shifts.

Prefer a video tutorial? Skip to the video.

Calculating Hours Worked

In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.

Excel timesheet with different rates for shift work

Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.

(D5-B5)*24

If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.

(1+D5-B5)*24 Continue reading

5 Alternative Reasons to Use COUNTIF Function

The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.

In this blog post will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are;

  1. Prevent duplicates in a range.
  2. Uniquely rank items in a list.
  3. Count the unique entries in a list.
  4. Compare two lists.
  5. Identify names that occur 3 times or more.

Continue reading

Sum Negative Values Only in a List

You may have scenario on a spreadsheet where you only want to sum the negative values in a range.

Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.

Sum negative values only

This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition (new to SUMIF? find out more).

The formula below will total the outgoings in the list by summing only the values less than 0.

=SUMIF(B2:B12,”<0″)

The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.

The criteria specifies that only values less than 0 should be summed.

Watch the video

Advanced Techniques for Summing Values

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

  1. Summing a range that contains errors
  2. Creating a cumulative total
  3. A consolidated sum from multiple sheets
  4. Summing Every Nth Value in a Range

Continue reading

Create a Picture Lookup in Excel

Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup to return a picture dependent upon the contents of a cell.

This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.

In this blog post we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.

Continue reading

Separate Text into Different Cells

A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.

This article looks at four techniques for separating text. Use the links below to jump to a specific technique.

Continue reading