Range of cells equal to a single value in Excel functions

Posted on 2014-09-01
Last Modified: 2014-09-29
Formula in Excel to display the date for the last occurrence of a value in a table


This is a follow-up question to the above thread. The formula given (by Flyster) for the solution was:


My question in this thread relates to the denominator of the 2nd term which includes the following:


How can a range of values be equal to a single value?

A range of cells implies multiple values, right? So I understand when a range is part of a function — such as:

        a) entering the formula =SUM(B1:B12) in cell B15 or

        b) asking if the function of a range is equal to the value in a single cell:  =IF(SUM(B1:B12)=B15,1,0)

But what does it mean to put a range of values equal to a single value?

Question by:WeThotUWasAToad
    LVL 47

    Expert Comment



    Returns a list of true or false depending on the values in the range and the value in B15
    for example

     would return

    LVL 31

    Assisted Solution

    by:Rob Henson
    When working with array formulas, the function looks at each value in a range as an individual value.

    So, the example posted, looks at each cell in the range B1:B12 and compares with B15 to generate the list of True/False values as described above.

    Rob H
    LVL 50

    Accepted Solution

    Hello Steve,

    You can see this in action using the "Evaluate Formula" Tool....or you can highlight a particular part of the formula and press F9.

    In this case if I have "x" in B15 and also have "x" in B4, B7 and B11 then when I select B1:B12=B15 in the formula bar and press F9 I see this array


    When you divide 1 by that array you get this as the lookup range:


    When you lookup 2 in a range that will never contain a value as high as 2 (as here because all values are 1s or errors) then the "match" is with the last number - in this case the 1 in position 11, so the corresponding value from A1:A12 is returned

    regards, barry

    Author Comment

    Rob, your statement,

    …the function looks at each value in a range as an individual value.

    really helps.


    Author Comment

    You can see this in action using the "Evaluate Formula" Tool

    Apologies for my belated reply but thanks for mentioning this tool. I've heard about it before but never really understood its value.

    I've made a couple of summary charts (for my own future reference) in the context of the four threads I started which are associated with this topic:

    • Formula in Excel to display the date for the last occurrence of a value in a table
    • Range of cells equal to a single value in Excel functions
    • Using an equation as a term in Excel functions
    • Reciprocal as an argument in Excel functions

    Also, for convenience, here is the screenshot showing the initial question:Initial Questionin which the goal is to display the date (in C15) for the latest or most recent entry (in the above table) of a user-defined value (B15).

    The best solution in that thread (imo) included the following formula:


    My 1st summary chart (moving from top to bottom) combines what is displayed in each of the evaluation steps when the Evaluate Formula tool is applied to the above formula:Results of Evaluate Formula toolMy 2nd summary chart (now moving from left to right) displays what I think is happening by setting B15 equal to the range B1:B12 and placing the result as the denominator in the term: 1/(B1:B12=B15):Subsequent StepsI would appreciate any comments regarding the steps which are displayed.
    LVL 80

    Expert Comment

    The sequence of operations is great as far as you took it.

    The remaining step requires understanding how LOOKUP works. LOOKUP compares the 2 in the first parameter to the values in the "Divide by 1" column. Not finding any match (or value larger than 2), it decides to focus attention on the very last match for a numeric data value. That means LOOKUP finds its match in row 9, and therefore returns the corresponding value from the "Value" column of your table: 07/11/14.

    I purposely used the phrasing "corresponding value" instead of value from the same row. Although they are one and the same in this problem, had you been returning a value from a different worksheet, the "corresponding" value would obviously not be in the same row.


    Author Closing Comment

    Extremely helpful. Thanks to everyone who contributed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now