financial modeling

The Excel Table Lookup Function Guide: XLOOKUP vs VLOOKUP vs INDEX & MATCH vs SUMIF(S) vs COUNTIF(S)

The Excel Table Lookup Function Guide: XLOOKUP vs VLOOKUP vs INDEX & MATCH vs SUMIF(S) vs COUNTIF(S)

Excel has several useful functions for retrieving and processing information from a table, but it can become confusing fast. This simple guide tells you when to use which.


XLOOKUP (for Office 365) and INDEX & MATCH are superior for table lookup

Girl looking through binoculars image

For looking up information from a table, XLOOKUP (for Office 365 users) or the combination of the INDEX & MATCH functions (for all previous versions of Excel) are superior to VLOOKUP or any of the other options listed above. This is because both XLOOKUP and INDEX & MATCH:

  • Can retrieve both text and numbers (unlike SUMIF, SUMIFS or SUMPRODUCT);
  • Can retrieve values that are sorted in either rows or columns (unlike VLOOKUP and HLOOKUP, which are limited to columns and rows, respectively);
  • Are not sensitive to column insertions & deletions (unlike VLOOKUP); and
  • Does not require the retrieval column to be to the right of the lookup column (unlike VLOOKUP).

For Office 365 users, XLOOKUP should be the go-to function when you want to look up information from a table. For earlier versions of Office or Excel, you will need to use the combination of INDEX & MATCH.

Most people shun INDEX & MATCH because it seems more complicated than the other functions. However, if you read on, I hope you’ll find it easier to use than you thought and you’ll add a powerful tool to your Excel toolbox.


INDEX & MATCH explained

I’d like to show you that INDEX & MATCH is not complicated at all. It combines two very simple functions.

Let’s have a look at each on its own, and then we’ll combine them.

INDEX

The INDEX function is a simple table lookup function. It retrieves a value from a range or array at the specified position. (Range refers to a single column or row, whilst array refers to a table with multiple columns and rows.)

Function syntax (square brackets indicate optional values. See end of post for more detail): INDEX(array,row_num,[col_num])

Here is an example to illustrate use of the INDEX function.

INDEX function example


MATCH

The MATCH function is a simple matching function. You specify a range (one row or column of values) and the value that should be matched, and the MATCH function will return the row or column number of the first instance of that value.

Function syntax (square brackets indicate optional values. See end of post for more detail): MATCH(lookup_value, lookup_array, [match_type])

Here is an example to illustrate use of the MATCH function.

MATCH function example


INDEX & MATCH together

When you combine the INDEX and MATCH functions, you get a flexible and powerful lookup function for any table. Use the MATCH function to determine the relative position in the lookup range of the value you are matching. This is then an input into the INDEX function to retrieve the corresponding value in the retrieval range.

Let’s work through a simple example to illustrate step-by-step how this works.

Say you have the same table as in the previous two examples:

INDEX & MATCH Excel function example image

Suppose you want to look up the league points for a team that you specify. Let’s assume it is Tottenham Hotspur.

So you want to match “Tottenham Hotspur” with a value in the range C4:C13 in the table above. This would be achieved as per the MATCH example above, i.e.:

=MATCH(“Tottenham Hotspur”,C4:C13,0)

This will return the result 5.

Now we insert this into the INDEX function to look up the league points at position no. 5 in the league points range:

=INDEX(D4:D13,5)

This will return the result 21.

It really is as simple as that! Let’s look at the combined function to do the matching and retrieval in one go:

=INDEX(D4:D13,MATCH(“Tottenham Hotspur”,C4:C13,0))

This will return the result 21.


XLOOKUP is INDEX & MATCH all in one

The XLOOKUP function combines the INDEX and MATCH functions in one. First it matches a lookup value to the same value in a lookup array and then it returns a corresponding item in the return array.

The XLOOKUP function syntax is as follows (see end of post for more detail):

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

If you omit [match_mode], the default value is 0, which is an exact match.

Let’s apply the XLOOKUP function to the same football league points table example from above:

=XLOOKUP(“Tottenham Hotspur”,C4:C13,D4:D13)

This will return the result 21.


All the functions in action

Let’s look at a bigger example to see all the functions in action. Here is an expanded premier league table:

Premier League table example

Here is a set of questions with answers produced using the various functions. The best function for each question is indicated by a bold-formatted answer.

And here is the set of formulas used to generate those answers:

You will note that some functions require a bit of gymnastics to make them do something that they were not designed for. I won’t discuss how these work here, but leave a comment if you want an explanation for any specific formula.

Here is a PDF download of the above three tables for this example in case you can’t read the above (direct download – no need to provide email address).

Do you prefer a different function or approach, or you have a better way of doing it? Let me know in the comments.

If you found this post useful, the course Financial Modeling for Entrepreneurs 201: Kickstart Your Spreadsheet Skills, teaches you everything you need to build spreadsheet-based financial models.


Summary of function syntax

Here is a brief summary of the functions discussed in this post (you can find more detail on each at Microsoft Office Support or Exceljet):

Moolman Institute logo


Photo by Edi Libedinsky on Unsplash

Posted by Sean Moolman in Financial Modeling, Spreadsheets, 0 comments
Test 4: How to Quickly Check Financial Viability of Your Business or Technology Idea

Test 4: How to Quickly Check Financial Viability of Your Business or Technology Idea

Can it become profitable?

[This post is part of a series of blog posts titled “6 Tests to Know Whether You Should Pilot Your Idea” and focuses on Test 4: Financial Viability. The full blog post series is available in a downloadable ebook. It is covered in more detail in the online course Opportunity Assessment for Entrepreneurs and Innnovators. Click here for a summary overview of all 6 Tests, here for the previous post (Test 3: How to Check Technical Feasibility of Your Business or Technology Idea) and here for the next post (Test 5: How to Develop a Basic Intellectual Property Strategy for Your Business or Technology Idea). Subscribe to the Moolman Institute newsletter (in the footer at the bottom of the home page) to be notified first when more content like this is posted.]


Why is this important?

Cash bunny imageThe need for financial viability should be obvious, but many people are so taken in with their ideas that they do not check this until late in the process.

This is often the case for ‘inventor entrepreneurs’ – people with a technical background that start with a technology idea, then transition into product development and finally entrepreneurship.

Of course your idea will morph over time, and “no plan survives first contact with customers”, but you also don’t want to start down a road if your input costs will be more than the market price you can achieve.

Far from being a damper on innovation, financial insights and constraints can provide design targets and stimulate further innovation. Hence it is vital to check financial viability early and often.

As Steve Blank says, “Putting together the financial model forces you to think about how to build a profitable business”.


How can I check this quickly at low cost?

Magnifier imageThe first rough estimate (A) will look at per unit profitability only. Should this be positive, a second estimate (B) is done using the unit economics approach.

As you develop your idea, you should return to the financial viability estimates again and again and rebuild or update your model based on new information and insights. You should also check if your assumptions are still valid.

A. Rough profitability estimate

Start with a rough estimate of profitability per unit of your product or service. You need cost and revenue estimates. Here’s how to do this step-by-step:

    1. List all the direct cost items you can think of (where direct cost refers to the cost of manufacturing the product or delivering the service). Examples include raw materials, components, service providers (that contribute directly to delivering the service), cloud services cost, customer acquisition cost and direct labour.
    2. Estimate the cost of each of the direct cost items. You should be able to obtain actual costs for all the major items (for example via online search or by calling suppliers). It is fine to estimate costs for smaller items. If you have a cost range, you can use the high end of the range to get a conservative view of profitability.
    3. Estimate the indirect cost as 25 – 50% of direct cost.
    4. Estimate the selling price. For incremental innovation (improvements to existing products and services), you can look at the current price of competitive products or services as a benchmark. For disruptive innovation (completely new products or services), it is a bit tougher to estimate selling price. You can gauge what customers might be willing to pay by asking potential customers. (Just keep in mind that, as discussed in the post on testing the market, what people say they would be willing to pay could be far removed from what they are really willing to pay.) A second way to estimate selling price for disruptive innovation is to look at the price of current alternatives (the cost of what people are currently doing to meet the need). This can help anchor your estimate.
    5. Estimate profitability . Calculate profit (profit = selling price – direct cost – indirect cost) and margin (margin = profit / selling price). You want to see a high positive margin (20% or more), since you are likely to have underestimated costs. If you have a negative or small positive margin, you need to relook at your assumptions, redesign your business model or rethink your idea.

B. Unit economics analysis

Unit economics imageUnit economics is an alternative lens through which you can view your idea’s financial viability. It is based around two concepts:

  • Customer Acquisition Cost (CAC) – how much does it cost you to acquire a customer?
  • Lifetime Value (LTV) of a customer – how much money do you make per customer?

As a bare minimum, LTV should be bigger than CAC. Otherwise your idea can never be profitable in the long run. Most VCs (venture capital companies) look for a LTV/CAC ratio of at least 3.

Here are three guidelines for how to do a unit economics analysis:

The unit economics model has some limitations, but remains a valuable customer-focused method for understanding the financial fundamentals of your idea.

Y-combinator CEO Sam Altman believes unit economics is critical for startups: “I think the answer is unit economics. One of the jokes that came out of the 2000 bubble was ‘we lose a little money on every customer, but we make it up on volume.’”


Real-world example

Homejoy logo image

Homejoy was an online marketplace for connecting customers with home cleaning services. They raised over $38 million in investment. They used a lot of this money to fuel rapid growth by providing a steep discount to first-time customers (charging $19 whilst their cost was $35).

Their bet was that first-time customers would become return customers (the ‘buy-your-customer’ model that Uber and Paypal and some others have used more successfully), but this was not the case. They kept on losing money on each customer (basic unit economics!) as well as losing workers to direct employment with customers, leading to their shutdown in 2015.

Exec, a mobile app to hire personal assistants for errands, went the same way.

The lesson? Better understand your unit economics.

Moolman Institute logo


In the next post in the series I discuss how to analyze your idea’s Intellectual Property position (Can you protect the idea? Should you? Do you have freedom to operate?).

Let me know in the Comments section what you think of this method or if you have a good example of where things went wrong based on the Technical Feasibility criterion.


This methodology is part of a Moolman Institute online course called Opportunity Assessment for Entrepreneurs and Innovators. The course guides you step-by-step through the 6 tests and provides you with a set of practical tools and templates to make it as easy as possible for you to get to product launch or idea demise.

If you would like more useful content like this or get notified when the next course launches, subscribe to the Moolman Institute newsletter on the home page.

Posted by Sean Moolman in Financial Modeling, Opportunity Assessment, Technology Commercialization, 1 comment