Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Range Lookup in Excel [Formulas]

Posted on June 30th, 2010 in Learn Excel - 120 comments

Here is a really tricky problem. Recently I was given a data set like this (shown below) and asked to find the position of lookup value in the list. The only glitch is that, instead of values, the lookup table contained lower and upper boundaries of the values. See the below illustration to understand what I mean.

Range Lookup Excel - Formula for looking up a value to match corresponding range

In simple words, we have to find the range that has the lookup value.

Now, the problem is similar to between formula trick we discussed a few days back, yet very different.

We all know that,

  • VLOOKUP formula looks up a value in a table and returns the corresponding value in next column
  • MATCH formula looks up a value and tells the position of it in a list

But neither seem to solve this problem. So I naturally turned to a cup of home brewed coffee (remember, I no longer work in a office, so I cant rush to espresso machine) and stared long and hard out of the window (remember, I no longer go to office, that means I can sit in front of a window and work).

Then I thought, “may be SUMPRODUCT formula would work for situations like these?!?”

After playing for a while, I got the perfect formula for this.

  • Assuming the value to be looked up is in cell C3
  • The start and end values are in B6:B15 and C6:C15 respectively,

We write,

=SUMPRODUCT(--(B6:B15<=C3)*(C6:C15>=C3),ROW(B6:B15))-5

Ok, go ahead, I will give you a minute to soak in the awesomeness of that formula.

Are you back?, well, lets explore what this formula does.

There are 3 portions in that formula,

  1. --(B6:B15<=C3)*(C6:C15>=C3) part: This is checking the range B6:B15 and C6:C15 to find that one set of start and end values that would contain the value in C3. The output would be a bunch of 0s with probably a single 1
  2. ROW(B6:B15) part: This just gives running numbers from 6 to 15. When you SUMPRODUCT this with above you get a single number corresponding the row in which the match occurred
  3. -5 part: We reduce the output value by 5 since our value began in row 6, not row 1.

Use this to lookup date ranges too:

Excel Vlookup Date Ranges - Excel Formula to lookup matching date ranges

As you can guess, you can easily use the above SUMPRODUCT formula to lookup matching date ranges too a la vlookup for date ranges.

Download Range Lookup Example Workbook:

In the download workbook, you can find both examples (values and dates). Go ahead and download it. Play with it to understand range lookup formula better.

Range Lookup Formula [XLS]

Do you face range lookup problem?

Often, when working on project planning, I end up checking where a date falls between given set of start and end dates. Earlier, I used helper columns to solve such a problem. But the SUMPRODUCT solution above is much more elegant and scalable. Plus it is much more fun to write.

What about you?

Do you face range lookup problem often? How do you solve it? Share your techniques and tips using comments. Thank you :)

More Excel Formula Magic:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

120 Responses to “Range Lookup in Excel [Formulas]”

  1. sig.frollo says:

    maybe it’s easier with MATCH function, but you need that data set is ordered. it seems ok with your examples. i made a try and worked fine for me
    first time i make a comment, but i really like your blog. good job
    i’m from italy, sorry for my english (and for world cup bad impression)

  2. Dominik Petri says:

    Hi Chandoo,

    I am also a big fan of the SUMPRODUCT formula :-)

    May I suggest 1 minor improvement to your formula? If you replace -5 by -ROW(6:6)+1 you can add more (or delete) header rows without changing your formula (or worse: getting wrong results from your formula).

    Usually after I’ve created a spreadsheet, my boss wants/needs more rows etc. Therefore, I (usually) never hardcode row or column offsets into my formulas (or VBA code). Using ROW() and/or COLUMN() works fine in VLOOKUP and OFFSET formulas as well… :-)

    Keep up the good work!
    Dominik.

  3. JP says:

    That formula is too long, LOL!

    MATCH formula does work:

    =MATCH(C3,B6:B15,1)

  4. Hui... says:

    @JP, Your solution gives the wrong answer if the date is outside a range, whereas Chandoo’s returns a Zero to tell you something is wrong

  5. scotty says:

    SUMPRODUCT is indeed awesome and when applied creatively can do many many things beyond simple matrix multiplication. However the mental gymnastics of arrays is beyond most Excel users, as are true array formulas. Hopefully once everyone is awesome at Excel this won’t be the case, but that won’t be any time soon …

  6. Hui... says:

    and Answering Chandoo’s question:

    6 months ago I answered a post at:
    http://chandoo.org/forums/topic/uni-assingment-help-matching-prices-for-shopping-items

    Where Sam, a student, wanted to be able to extrapolate between 2 numbers which were based on other criteria, resulting in both a horizontal and vertical offset of both the X and Y components.

    After 19 posts I came up with:
    =+TREND(OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1),+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1)-1,+MATCH($D7,OFFSET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7)

    It is described in the post and it did my head in while working it out.

    I agree with JP that it is too long.

    I hope Sam got a good mark for his assignment !

  7. Hui... says:

    That should be:
    =+TREND(OFFSET($J$6,+MATCH($C7,$J$7:$J$16,1),+MATCH($D7,OFF
    SET($K$6:$P$6,MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),OFFSET($J$6,
    +MATCH($C7,$J$7:$J$16,1)-1,+MATCH($D7,OFFSET($K$6:$P$6,
    MATCH($C7,$J$7:$J$16,1)-1,0)),1,2),$D7)

  8. JP says:

    Well met, Hui. My solution requires that the dates be all-inclusive, i.e. there should be no gaps between the date in C6 and the one in B7.

  9. JP says:

    Here’s what I came up with using MATCH, it’s ugly but seems to work (I’d be glad to hear otherwise):

    =IF(MATCH(C3,C6:C15,1)+1 = MATCH(C3,B6:B15,1),MATCH(C3,B6:B15,1),””)

    We can remove the hardcoding from Chandoo’s formula like this:

    =SUMPRODUCT(–(B6:B15=C3),ROW(B6:B15))-(ROW(B6)-1)

    By the way, Chandoo’s formula returns -5, not 0, if it doesn’t find a match.

  10. Elias says:

    @JP, Your solution gives the wrong answer if the number is between the first range (10 – 25).

    One more option with Match.
    =IF(MAX((B6:B15=C3)),MATCH(1,(B6:B15=C3),0),””)
    Confirm with Ctrl+Shift+Enter

    =IF(MAX(INDEX((B6:B15=C3),0)), MATCH(1,INDEX((B6:B15=C3),0),0),””)
    Confirm with just Enter

    Regards

  11. Elias says:

    Formulas don’t look right.

    One more option with Match.
    =IF(MAX((B6:B15 “LE” C3)*(C6:C15 “GE” C3)), MATCH(1,(B6:B15 “LE” C3)* (C6:C15 “GE” C3),0),””)
    Confirm with Ctrl+Shift+Enter

    =IF(MAX(INDEX((B6:B15 “LE” C3)*(C6:C15 “GE” C3),0)), MATCH(1,INDEX((B6:B15 “LE” C3)* (C6:C15 “GE” C3),0),0),””)
    Confirm with just Enter

    Note: Replace “LE” for “<= ” and “GE” for “&gt=”

  12. Daniel Ferry says:

    Chandoo,
    .
    Since the first term of your SUMPRODUCT formula includes an arithmetic operation (*), there is no need to coerce the term’s booleans to numeric with the double unary. The arithmetic operation does this already.
    .
    There is also no need to adjust the row number with the -5 part. Instead just have the second term start in Row 1:
    .
    =SUMPRODUCT((B6:B15=C3),ROW(B1:B10))
    .
    It is true when using SUMPRODUCT that all of the arrays need to be of the same size, BUT the do not need to line-up on the same rows. Notice that all three are 10 rows tall.
    .
    Doing it this way has the advantage that the formula WILL return a zero, not a -5, if it doesn’t find a match.
    .
    You can then change your formula on the sample spreadsheet that reports which row (or none) a value was matched to:
    .
    =”Row no. ” & SUMPRODUCT((B6:B15=C3),ROW(B1:B10))
    .
    with the understanding the “Row no. 0″ means no match was found. This eliminates doing two SUMPRODUCTS and the IF…
    .
    I write about SUMPRODUCT in detail on my blog:
    http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html
    .
    Regards,
    Daniel Ferry
    excelhero.com

    • Kaushik Chatterjee says:

      Hi, i am using a data base with following details:
      Row. B2 to Z2 : date range say 1st to 25th
      Row no. 3 to 28 having sales amt against each date for each store
      i want to know who calculate sales figures for a particular date range say 3rd to 10th of a particular store in a difference sheet (with Vlookup formula).
      can you team pls guide me

  13. Daniel Ferry says:

    Wow. Not sure how the two formulas got clipped but in my comment above, they should have been:
    .
    =SUMPRODUCT((B6:B15=C3),ROW(B1:B10))
    .
    and
    .
    =”Row no. ” & SUMPRODUCT((B6:B15=C3),ROW(B1:B10))
    .
    Regards,
    Daniel Ferry
    excelhero.com

  14. Daniel Ferry says:

    Well they got clipped again. I’ll try one more time:


    =SUMPRODUCT((B6:B15<=C3)*(C6:C15>=C3),ROW(B1:B10))

    .
    and
    .

    ="Row no. " & SUMPRODUCT((B6:B15<=C3)*(C6:C15>=C3),ROW(B1:B10))

    .
    Regards,
    Daniel Ferry
    excelhero.com

  15. Hui... says:

    reminds me of an old Chubby Checker song…
    “Clipped again, like we did last summer”

  16. Daniel Ferry says:

    @Hui –

    Ha. That will teach me to remembered to use the entity codes for the inequalities.

    Regards,
    Daniel Ferry
    excelhero.com

  17. Steve says:

    Hi Guys,
    This relates to a problem I am having. How can you COUNT the number of times a set of dates occur within a range of dates. For example: If you have several rows and columns with dates in each cell, and you want to know how many times a date lies between 6/1/2010 and 6/30/2010, how would you calculate this with a formula?? I have been racking my brain for a while using different methods….

    Please help!

    Thanks

  18. Daniel Ferry says:

    @Steve –


    =SUMPRODUCT((C2:G100>=DATEVALUE("6/1/2010")) * (C2:G100< =DATEVALUE("6/30/2010")))

    ...assuming your dates were listed in C2:G100.

    If you defined your date thresholds in A2 and B2 you could shorten this to:


    =SUMPRODUCT((C2:G100>=A2) * (C2:G100< =B2)

    Regards,
    Daniel Ferry
    excelhero.com

  19. Steve says:

    Thanks, Daniel!

  20. Mark says:

    All I can say is “elegant”.

  21. jeff weir says:

    Don’t even need sumproduct. Could just use =MAX(($C$3>=$B$6:$B$15)*($C$3<=C6:C15)*ROWS($B$6:$B$15))

  22. jeff weir says:

    …which should be array entered.

  23. jeff weir says:

    Actually I’m telling lies…the ROWS bit doesnt work. Instead should use the ROW method proposed by Daniel.

    {=MAX(($C$3>=$B$6:$B$15)*($C$3<=C6:C15)*ROW($B$1:$B$10))}

  24. Daniel Ferry says:

    @jeff –
    .
    Good observation! Couple of things. I know you meant ROW and not ROWS. I would change the range inside of ROW to B6:B10 so it produces the expected row number. And for others trying to follow along, Jeff’s formula is a CSE formula and must be array-entered:

    =MAX((C3>=B6:B15)*(C3<=C6:C15)*ROW(B1:B10))

    .
    Regards,
    Daniel Ferry
    excelhero.com

  25. Daniel Ferry says:

    @jeff -
    .
    I see you beat me to the punch!

  26. jeff weir says:

    You can actually do this with VLOOKUPs.

    =AND(VLOOKUP(C4,B7:D16,3,TRUE),((C4)<=VLOOKUP(C4,B7:D16,2,TRUE)))*VLOOKUP(C4,B7:D16,3,TRUE)

  27. jeff weir says:

    Forgot to mention that this requires you to add the numbers one through 10 down the right hand side of your table. i.e. in D7:D16

  28. jeff weir says:

    or you can do away with the helper row and use this:
    =AND(MATCH(C4,B7:B16),((C4)<= VLOOKUP(C4,B7:C16,2,TRUE))) *MATCH(C4,B7:B16)

  29. Chandoo says:

    Wow.. I am thrilled with the discussion so far. Excellent stuff…

    Some general observations before I move to specific comments:

    (1) My dataset was huge and it had several gaps.
    (2) I wasnt sure if the data is sorted (that eliminates MATCH technique relying on sorted lists)
    (3) I usually prefer non array formulas (technically SUMPRODUCT is not an array formulas as I am not pressing ctrl+shift+enter) :D

    Very insightful comments from JP, Hui, Jeff, Elias and Daniel so far… Thank you for teaching valuable tricks…

    @Sig.Frillo: Welcome to Chandoo.org. You need not be ashamed. I am from a country that never even qualified for World Cup football.

    @Dominik: Excellent suggestion. I tried to keep the formula short for this post as it lets more readers get it. So I used a bit of hard coding.

    @Scotty: I am trying hard, almost each day so that most of us can be awesome in excel :)

    @Hui… Thanks for the pointer to discussion on forums. Excellent stuff. Just read it to learn about the TREND formula (didnt know it was there)

    @Elias: Sorry, the commenting engine is crazy and often chops the lessthan and greater than symbols thinking they are HTML code. Good stuff btw…

    @Daniel: Good observations on the formula. Also, your formula “=SUMPRODUCT((B6:B15< =C3)*(C6:C15>=C3),ROW(B1:B10))
    ” is much more clean and elegant. Hats off.

    @Mark.. thank you :)

    @Jeff: very elegant stuff. very elegant.

    one quick and dirty way to get rid of ctrl+shift+enter is to wrap your formula inside SUMPRODUCT like this =SUMPRODUCT(MAX((C3>=B6:B15)*(C3< =C6:C15)*ROW(B1:B10)))

  30. jeff weir says:

    …or you can use this (though I don’t know why you would want to):
    =MATCH(C4,B7:B16)*(SUM((INDIRECT(ADDRESS(B7,1)):INDIRECT(ADDRESS(C7,1)),INDIRECT(ADDRESS(B8,1)):INDIRECT(ADDRESS(C8,1)),INDIRECT(ADDRESS(B9,1)):INDIRECT(ADDRESS(C9,1)),INDIRECT(ADDRESS(B10,1)):INDIRECT(ADDRESS(C10,1)),INDIRECT(ADDRESS(B11,1)):INDIRECT(ADDRESS(C11,1)),INDIRECT(ADDRESS(B12,1)):INDIRECT(ADDRESS(C12,1)),INDIRECT(ADDRESS(B13,1)):INDIRECT(ADDRESS(C13,1)),INDIRECT(ADDRESS(B14,1)):INDIRECT(ADDRESS(C14,1)),INDIRECT(ADDRESS(B15,1)):INDIRECT(ADDRESS(C15,1)),INDIRECT(ADDRESS(B16,1)):INDIRECT(ADDRESS(C16,1))) INDIRECT(ADDRESS(C4,1))))^0

    All those indirects and addresses are purely a long-winded way of finding out if the value in C4 falls in any of the ranges.
    This is based on the fact that if you write say =$A$1:$A$10 $A$9 then excel treats the space as an intersection operator, and gives you the intesect of these two ranges. I.e. you get the value in $A$9. More on this at http://www.excelhero.com/blog/2010/06/which-function-to-use—part-1.html

  31. Hui... says:

    Don’t worry about the world cup,
    I am from a country that did qualify and am ashamed !

  32. Cyril Z. says:

    Hello Chandoo,

    This is a really awesome stuff I can already think to integrate to my Gantt Datasheet… since tasks often don’t overlap.

    @Sig.Frillo and @Chandoo : People in countries not qualified have more time to play with Excel. This is the reason why real excel heroes come from overseas. :-)

  33. Bora says:

    Thanks for the great work Chandoo !! :)

  34. holly says:

    i am looking for help – have crew list name – from and to dates and rank looking to find crew replacement to date to match from date and rank any help appreciated

  35. Lew B says:

    Great formula… I was wondering how to include a calculation in this formula. For example, I’m calculating the miles I drive for business trips to the monthly mileage rate allowed by the company. Because the mileage rate changes monthly, I’ve created a table for each month and the associated mileage rate:

    Month Begin Month End Mileage Rate
    01/01/2011 01/31/2011 $0.45
    02/01/2011 02/28/2011 $0.42
    03/01/2011 03/31/2011 $0.46

    In another table I record my mileage and the actual Date of the business trip. In this table I will have a row for each trip and I will have many rows per month. I included the following formula from your example and it does provide the “Row Number from the above monthly table. What I would like it to do is to use your formula to find the month (which it does) and instead of giving me the row number, I would like to insert a calculation to multiply the Mileage rate for that specific month times the actual mileage I recorded.

    =IF(SUMPRODUCT(–($I$8:$I$19=M8))=1,”Row No. “&SUMPRODUCT(–($I$8:$I$19=M8),ROW($I$8:$I$19))-7,”Not Found”)

    Any thoughts? Thanks…

  36. Chandoo says:

    @Lew: You can easily add the multiplication part. Use this formula,

    =IF(SUMPRODUCT(–($I$8:$I$19=M8))=1,INDEX($K$8:$K$19,(SUMPRODUCT(–($I$8:$I$19=M8),ROW($I$8:$I$19))-7)*N8,0)

    This assumes, the mileage rate is in K8:K19 and Miles driven is in N8.

  37. Jesse Walker says:

    Hi All,

    Has anyone tried doing this with a set of data in columns instead of rows? Let me preface this by saying “I’m a novice still working through Excel School” :) I have a project gantt that I’m trying to track several projects in from start date to end date–those are in columns. Then along the top row of the gantt are the months of the year. I’m having a HORRIBLE time figuring out how to get my gantt to recognize that although March 15, 2011 is not before March 1, 2011–it IS in the month of March and should therefore highlight!

  38. Jesse Walker says:

    I should also mention that I have a range of dates–e.g., March 15 – 2011 (start date) through July 19, 2012 (end date), that I’m trying to conditionally format (highlight)… I’m only getting it to recognize either April through July (seemingly because March 15 falls after March 1) OR March 1–but not both…

  39. Hui... says:

    @Jesse
    Can you post your data somewhere together with a description or example of what you want to achieve.

  40. Jesse Walker says:

    Hi Hui,

    I’ve posted the URL for the spreadsheet–hopefully it came through ok? The Gantt should have several projects (approx. 17) with Start and End dates. What I’m trying to do is show the amount of time they are estimated to take (from start to end), based on the Start/End dates. So, for the first one–I’m trying to get the Gantt cells to turn a color starting March 2011 and go through July 2012 to reflect the time of the program. This data for the Gantt is all pulled from the Raw Data sheet. I can email you the file directly if it doesn’t come through.

    THANK YOU!!

  41. Shannon says:

    I know I’m a bit late to this discussion, but I’m hoping someone can help me with a variation on the range theme.

    Rather than returning the row that the lookup value is in I’d like to be able to return a value that relates to each range. For example for 10 to 25 units the price is $150, but for 30 to 40 units the price goes down to $125. I want the price to populate when the number of users is entered.

    Any help would be great, thanks!

  42. Abhishek says:

    This worked beautifully.
    Thanks.

  43. Ross Fuller says:

    Two further question on this which I’m struggling with. I can normally figure these things out but am blocking on this one. I want to get the data at the intersection of a row and a column based on a number falling within a given range. In other words, the information provided is a great start. However, I want to do two additional things.

    1. I want the row array in the formula to be dynamic. My table position may change so the row number may change. How do I circumvent this in the formula?

    2. I want to return both the row number as well as a column number to the right of the data above. Using the example data, suppose I have data in column E. How do I get the data from the intersection of the row (using the range match) and a specified column?

  44. Hui... says:

    @Ross
    Can you upload a file with examples and notes of what your after
    refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

  45. Kari says:

    Wow, this is all really great learning. It solves somewhat of my problem but I don’t think the whole thing. I am trying to get a formula to determine a year ago from the last date entered into my worksheet. i.e. – 1/16/12 is the last date entered, I need excel to then grab any dates from 1/16/12 back to 1/16/11. Then, I have a column next to those dates with numbers. Once I have the correct dates for the past year, I then need to add the numbers in the next column to figure out the time the employee used within the past year. It seems a VLOOKUP should do the trick but I can’t seem to get it right. Please help! Thanks.

  46. Abhinav says:

    If you sort the forst column in an ascending order, (which I see will already be done), a simple vlookup formula will do, the formula will typically look like =vlookup(‘lookup value’,’lookup range’,3,true)

    This will basically lookup the next highest value that is less than the lookup value in the first column and return the corresponding column 3 data.

    Hope this helps

  47. Glenn says:

    Thank you SO much: just what I needed! :-D

  48. Murad says:

    I want to find the total numbers between two numbers.
    Example:
    Row contains the numbers – 1,2,3,4,5,6,7,8,9
    I want to know total numbers in between 3 & 7
    Answer I should get is 3.

    Regards
    Murad

    • Chandoo says:

      @Murad… you can use COUNTIFS to do this. Assuming the list of numbers is in A1:A10, use =COUNTIFS(A1:A10,”>=3″,A1:A10,”<=7″) gives you the count of numbers between 3 and 7.

  49. William says:

    Hi Chandoo,
    A very useful explanation – and some good discussion.
    I have a similar problem, for which I haven’t yet found a solution. Consider a table that looks like this:
    BAND VALUE
    100 10
    200 5
    300 4
    500 3
    VLOOKUP will correctly return the value of 10 for a number in the range of 100-199, 5 for a number in the range 200-299, etc. However, if a number such as 400 is the target, I need it to find the value associated with 500, namely 3. Notice that there isn’t a row starting with 400.
    I tried this using MATCH and INDEX, but no joy. Any ideas?

  50. Skip says:

    What if the date range table had a third column of labels?
    For instance
    Name Start End
    Bob 1/1/2012 1/15/2012
    Bill 1/5/2012 2/4/2012
    Jane 8/14/2011 9/4/2011
    Bob 9/19/2011 9/25/2011
    Bill 8/25/2011 9/25/2011

    Now I want to get the period associated with 1/10/2012 for Bob. Therefore it should not return Bill’s period 1/5/2012 to 2/4/2012. Instead, it should return the first entry, 1/1/2012 to 1/15/2012.

    Also, I have a second question. For bonus points, I would like to know if Bill on 9/16/2012 has more than one associated periods. For instance, if there was one more entry
    Bill 9/10/2011 10/3/2011
    Then the 9/16/2012 is covered by two different periods. How could I get both of the periods? I did this in SQL with some outer joins, but I was wondering if it is possible right in excel. This will probably be an array formula because it will return an unknown number of rows for each lookup. In the end, this is probably easier solved with SQL, but I am still curious to know how it could be solved with excel formulas.

  51. Skip says:

    whoops, for the second question, the date in question is 9/16/2011.

  52. Rakesh Tendulkar says:

    Hi,
    I have data of work done everyday in one worksheet, in which Column A contains dates (eg. 01-03-2012, 02-03-2012 etc.), col. B = Hull Nos.(eg. BH102, BH103, BH-104 etc.), col. C = process (eg. Casting, Assembly etc.). Now in another worksheet when in cell A1 enter Hull No. eg. BH102, in another cell A2, formula should pick start date i.e. first date when that Hull No. work has been started. Which formula i should use?
    thanks

  53. Smith says:

    I don’t know if anyone is still monitoring this page, but I have a question? I have been looking all over the internet for a formula that looks at a range and tells you if it is inside another range. for example compare X:X to -5:5, and if it is inside the -5 and 5 then it is true, outside false. A few details are I am trying to see is if a time frame is inside another frame, the time is a little messed up though. noon is -511 and midnight is 511. 6am is 0 and 6pm is 0, the range is -5 to 5. Sorry if this is a little confusing but I hope you can help.

  54. Shwetang says:

    Hi All,

    The formula is working fine if the date is appearing only in one range, however it is not working when your date will appear in more than one range. Any ideas why?

    Thanks.

  55. Zaib says:

    Hi Guys,

    I am new to this forum,but was trying your formulas..But somehow for my case, the “SUMPRODUCT” is not working,,,

    I want to search 12:13am in the range for the following data,
    Start End
    7:31:00 AM 8:30:00 AM
    8:31:00 AM 9:30:00 AM
    9:31:00 AM 10:30:00 AM
    10:31:00 AM 11:30:00 AM
    11:31:00 AM 12:30:00 PM
    12:31:00 PM 1:30:00 PM
    1:31:00 PM 2:30:00 PM
    2:31:00 PM 3:30:00 PM
    3:31:00 PM 4:30:00 PM
    4:31:00 PM 5:30:00 PM
    5:31:00 PM 6:30:00 PM
    6:31:00 PM 7:30:00 PM
    7:31:00 PM 8:30:00 PM
    8:31:00 PM 9:30:00 PM
    9:31:00 PM 10:30:00 PM
    10:31:00 PM 11:30:00 PM
    11:31:00 PM 12:30:00 AM
    12:31:00 AM 1:30:00 AM
    1:31:00 AM 2:30:00 AM
    2:31:00 AM 3:30:00 AM
    3:31:00 AM 4:30:00 AM
    4:31:00 AM 5:30:00 AM
    5:31:00 AM 6:30:00 AM
    6:31:00 AM 7:30:00 AM

    I have tried
    =IF(SUMPRODUCT(–(I6:I29=J3))=1,””&SUMPRODUCT(–(I6:I29=J3),ROW(I6:I29))-5,”Not Found”)

    but no success, Can somebody help me?

    This is working for time only before 11:30 pm,
    Thanks,

    • Debraj Roy says:

      Hi Zaib,
      It showing “Not Found” because, There was no slot you mention for 12:00 AM to 12:30 AM.
      Adjust the slot, it will work perfectly..:)

  56. james says:

    Hello,

    I need help on an IP Range Lookup.

    I have a bunch on IP addresses log that needs to be match from a List of IP address database

    E.g

    Database

    Equipment    Starting IP    Ending Ip address    Available Host
    AAA        10.0.1.1    10.0.1.254        510
    BBB        10.0.22.1    10.0.23.254        510
    CCC        10.4.28.1    10.4.31.254        1022
    DDD        10.6.8.1    10.6.11.254        1022

    The available host is just for indication that the range can contain either 510 hosts or 1022 hosts. The subnet mask is either 255.255.252.0 for 1022hosts or 255.255.254.0 for 510

    Now let say I want to find an IP address 10.4.30.123 which is located in IP range for Equipment CCC.
    How do I proceed and what will be the easiest way.

    Thanks in advance

    • Debraj Roy says:

      Hi James,
       
      Assuming your data was in A3 to D7.
      @ B1 type the IP Address, and @ C1 Type formula as
      {=INDEX($A$4:$A$7,MATCH(1,(B1>=$B$4)*(B1<=$C$4:$C$7),0))}
      Note that after enter formula, press Ctrl + Shift + Enter
      It will provide you desired Result..
       

  57. simon says:

    Hi I am looking to do the same. Did you ever get it sorted?

  58. Antoon Leurs says:

    A problem I face with is close to the example given at the start of the discussion. I need a formula which counts the number of times a certain time dd/mm/yyyy (this time a calculated sum of two other cells) falls between the time slots given in columns.  I have a column with start time and a column with end time like the given example.
    columns are
    ID —–START TIME ——END TIME
     
    How do I solve this problem?
    Thanks in advance

    • Chandoo says:

      Assuming the certain time is in cell A1, and your list is in B2:B10 (start), C2:C10 (end), you can use below formula:

      =countifs(B2:B10,”>=”&A1,C2:C10,”<=”&A1)
      This tells you how many times A1 falls in the range of start and end times.

  59. Josh says:

    Hello,
    Thanks for the great notes on range look ups, however my problem runs deeper.  I work for an aftermarket automotive company and I work with year ranges on vehicles.  So I get data generated from our system that looks like this:  

    AUDI
    A6 Allroad
    01-05

    AUDI
    A6/S6
    05-11

     
     
     
     
    05-11

    I then am able to pull data from our website that looks like this: 

    2003
    AUDI
    A6 Allroad 

    Is there a way for me to search the for-mentioned vehicles (with the range) by the individual year listed vehicles that I can generate from the web?

    Thanks for any help you can give! 

  60. Matey says:

    I need a formula that will look at a value, find the range that this value falls within and then assign a name according to the range. The value ranges that I am interested in look like this:

    <100k 
    >100k <=500k
    > 500k <= 5m
    > 5m <= 25m
    >25m 

    If the value is <100k, I need the answer to say “Minor”
    If the value is between 100k and 500k, I need the answer to say “Moderate”
    If the value is between 501k and 5m, I need the answer to say “Major”
    If the value is between 5m and 25m, I need the answer to say “Severe”
    If the value is > 25m, I need the answer to say “Critical”

    Any suggestions?
     

    • KESDEC21 says:

      YOU MAY USE VLOOKUP TO RESOLVE IT…..
      1ST STEP MAKE A TABLE

      0
      “Minor”

      100
      “Moderate”

      500
      “Major”

           5,000,000.00
      “Severe”

         25,000,000.00
      “Critical”

       
      AND THE USE VLOOKUP WITH TRUE TEST……
       
       

  61. Sanjeev Aggrwal says:

    “-5″ of last part may need some improvement as is hard coded. May be we can use offset function.

  62. shaker says:

    hi 
    i am looking for date formula: in  my project they will provide me end contact date and months. from this i need to find the out the start date
    for eg: end contract date is : 2/2/2015 and  eg number of months is 55 then i will find the start contract date.
     
    Regards
    Shaker

  63. Sobuj says:

    Hi all, 
    I’m trying to solve a problem which is somewhat looks like (only a portion of it…) ,
    A          B          C           D        E
    8          6          10         17         
    9          11        18          9
    10        19        25          7
    where Column B  is the starting position and Column C is the ending position and in Column D there are some variable value. Now I’m trying to genarate a formula which will check the value of Column D and will determine in which range it falls and then it will return value from Column A of the corresponding Row where the match was found in Column E. Such as for the value of 17 in Column D it’ll return 9 as because this value falls between 11-18. So far I was able to develop something by using if function but it’s not practical for large amount of data so I was wondering that if you guys can help me out.
    Thanks in advance

  64. sri says:

    Hi, I am looking for a function in excel which will place a referenced cell value, if a date range falls within another date range. Can anyone help me? Thanks in advance. Sri

  65. i want to submit my excel formula problem…. and wait for your appreciable solution

  66. Kapil says:

    Dear Chandoo,

    I Have table with one column of Student marks and one column of Grade. Can u please help me to find the Upper limit and lower limit for a particular grade.

    Thanks,

    Kapil

  67. Janelle says:

    GROSS SERVICE SALES ALOWENCE

    $0 – $12,000 $50.00
    $12,001 – $16,000 $65.00
    $16,001 – $20,000 $80.00
    $20,001 – $24,000 $85.00
    $24,001 – $28,000 $110.00
    $28,001 – $32,000 $125.00
    $32,001 + $140.00

    This is on sheet 1 of my workbook. On sheet 2 I have:
    Salon Sales Cost Allowance Total
    153 $15,275.60 $194.62 $65.00 $259.62
    160 $23,285.60 $217.30 $85.00 $302.30
    166 $12,774.60 $133.57 $65.00 $198.57

    I would like the allowance to automatically happen based on the sales total.
    Any help?
    Thanks

  68. JP says:

    how do I calculate IF(C16″Day’s”,B15+B16,DATE(YEAR(B15),MONTH(B15)+B16,DAY(B15)))

  69. JP says:

    Hi
    how do I calculate =SUM(L5:N5,L7:N7,L9:N9,L11:N11,)-MIN(L5:N5,L7:N7,L9:N9,L11:M11)=COUNT(L5:N5,L7:N7,L9:N9,L11:N11)-1.5

  70. JP says:

    C D E
    4 Test 1 Test 2 Test 3
    5 103 110 110
    6 Test 4 Test 5 Test 6
    7 105 100
    8 Test 7 Test 8 Test 9

    10 FINAL
    11 106 106 53

    I’d like to convert this 3 in 1
    =SUM(C5:E5,C7:E7,C9:E9,C11:E11,)-MIN(C5:E5,C7:E7,C9:E9,C11:D11)
    =COUNT(C5:E5,C7:E7,C9:E9,C11:E11)-1.5
    =B11/B12)

    • Hui... says:

      @JP
      I don’t understand what you mean by convert this 3 in 1?
      Do you want to combine the formulas like:
      =(SUM(C5:E5,C7:E7,C9:E9,C11:E11,) -MIN(C5:E5,C7:E7,C9:E9,C11:D11)) / (COUNT(C5:E5,C7:E7,C9:E9,C11:E11)-1.5)

  71. JP says:

    yes id like to combine it but it still not works?

  72. JP says:

    thanks’ a mill I got it!

  73. Steve Vocke says:

    You guys are awesome so I’m hoping you can help with this one. It is similar to a couple mentioned above.
    I have a company that run 2 12 hour shifts per day, 7 days per week so it takes 4 different teams. I have assigned each shift with a shift name, A, B, C, D.
    Here is what an example schedule would look like.
    Monday D 4-Mar 0:00 5:59
    Monday A 6:00 17:59
    Monday C 18:00 23:59
    Tuesday C 5-Mar 0:00 5:59
    Tuesday A 6:00 17:59
    Tuesday C 18:00 23:59
    Wednesday C 6-Mar 0:00 5:59
    Wednesday B 6:00 17:59
    Wednesday D 18:00 23:59
    Thursday D 7-Mar 0:00 5:59
    Thursday B 6:00 17:59
    Thursday D 18:00 23:59
    Friday D 8-Mar 0:00 5:59
    Friday A 6:00 17:59
    Friday C 18:00 23:59
    Saturday C 9-Mar 0:00 5:59
    Saturday A 6:00 17:59
    Saturday C 18:00 23:59
    Sunday C 10-Mar 0:00 5:59
    Sunday A 6:00 17:59
    Sunday C 18:00 23:59

    What I need, if I put a date and time in a specific cell, I need to to return the shift name back.

    Input Return
    3/6/13 6:15 B

    Thank you!

  74. Jeff Weir says:

    Revisiting this post.
    Won’t this do it:
    =MATCH(1,(LookupValue=Min_Range),0)

  75. Jeff Weir says:

    Damn. Parser ate my bits.

    =MATCH(1,(A2 less than or equals to Max_Range)*(A2 greater than or equals to Min_Range),0)

  76. Jeff Weir says:

    Forgot to say needs to be Array entered.

  77. Champ says:

    Hello,
    I have the following problem. In one list there are the material numbers and the amount sold of these. In the other list there are the prices for each material, however, these are subdivided into a few rows always starting with the according material number showing different prices according to the amount sold (decreasing with increasing amount sold).
    My question now is how to consolidate these two list. So that for each amount sold it is shown the respective price depending on the amount sold.

    Thanks a lot

  78. Rajesh says:

    I have a set of dates in a column that span an entire year and some more data in adjacent columns against these dates. I want to first find out the last date of each month and pick up some data from other columns against that particular date and from the row above. The problem is that the last date in the series may not be the last date of the calendar month and is usually variable. Can this be done?
    Thanks in advance

    • Chandoo says:

      Hi Rajesh…

      You can use INDEX + MATCH formulas in this situation.

      Assuming dates & values are defined names and month is in cell E2, use

      =INDEX(values,MATCH(MONTH(E2),MONTH(dates),1))

      Press CTRL+Shift+Enter when typing this formula.

      • Rajesh says:

        Thanks Chandoo,
        I know I am doing something wrong as I am getting #REF error. When I put my cursor in the formula bar, it correctly shows the values (closing prices from historical stock prices downloaded from Yahoo! Finance) but the column that contains dates (defined as =OFFSET(table!$A$2,0,0,COUNTA(table!$A$300),1)) does not get highlighted but I cannot figure out what it is.
        I also tried changing the format of the month to September and then 9 and 09 but to no avail.

  79. Nhan says:

    Hello,
    I have a problem that similar to some of yours. The twist is that my data range are postal code. So the formula is not valid since a postal code has number and letter.
    HELP!

    Regards,

  80. Nhan says:

    @ Hui
    Let say I have to ship a box to a location where the postal code is K1A2H5 the price should be 90.
    Here are the interval/ranges of postal code with their respective rate. The Sumproduct formula is good for number but when I have this type of data it doesn’t work.

    Interval/Range $
    G1A0A0 G2N9Z9 50
    L3X0A0 L3Y9Z9 60
    K0A3H0 K1A3H0 90
    K6H0A0 K6K9Z9 150
    K6A0A0 K6A9Z9 75
    M1A0A0 M9Z9Z9 35

    Is there another formulas that can match in which range the destination is and then lookup to the 90 for this example?

    Regards,

    • Chandoo says:

      @Nhan…

      You can use SUMIFS() formula in this case.

      Like below.

      Assuming interval starts are in the range start,
      interval ends in the range end
      $s in the range dollars
      and value you want to lookup is in A1

      write =SUMIFS(dollars,start,”< ="A1,end,">=”&A1)

      will return 90 for your example.

  81. Nhan says:

    @Chandoo

    I tried and the value I get is 0.
    The formulas goes like this:
    =SUMIFS(C4:C9,A4:A9,”=”&A1)

    According to this:
    K1A2H5 0

    Start End $
    G1A0A0 G2N9Z9 50
    L3X0A0 L3Y9Z9 60
    K0A3H0 K1A3H0 90
    K6H0A0 K6K9Z9 150
    K6A0A0 K6A9Z9 75
    M1A0A0 M9Z9Z9 35

  82. Nhan says:

    Sorry the formulas goes like this:
    =SUMIFS(C4:C9,A4:A9,”=”&A1)

  83. Angela says:

    % achieved % KPI

    140% 160%
    135% 140%
    130% 135%
    125% 130%
    120% 125%
    110% 110%
    100% 105%
    90% 100%
    85% 95%
    80% 90%
    75% 85%
    70% 80%
    0% 80%

    Hi – I need to find a formula that will calculate what to pay someone when they achieve within a set range. I have the ranges i have to work with above. my concern is that if someone achieves 104% ten it should be calculating what to pay them based on 104% not on the 100% or 110% amount. how can i do this?

  84. Sharmita says:

    Hi

    Could you please let me how to find the approximate value using vlookup for the below Tax Band:

    Fixed Variable
    Lower Upper Amount Percentage
    0 10,000 0 0%
    10,001 20,000 10 5%
    20,001 30,000 20 10.0%
    30,001 40,000 30 15.0%
    40,001 50,000 40 20.0%
    50,001 60,000 50 25.0%
    60,001 70,000 60 30.0%

    We have to find the below for the given details:
    Taxable Amount Fixed Amount Variable Percentage
    10,001
    16,579
    23,157
    29,735

    I am able to calculate the fixed amount for the given taxable amount using the vlookup formula but unable to calculate the variable percentage the value returned is 0.

  85. Nhan says:

    Hi,

    I`m trying to Index/Match the Code to use in column A when I enter a postal code in cell C1.

    There’s an error in this formulas that I can solve.

    = IF(SUMPRODUCT(–(B4:B13=1)),INDEX($A4:$A13,MATCH(SUMPRODUCT(–(B4:B13=1)),C4:C13,0),”Not Found”)

    Example:

    Postal code L1T9Z9
    Code to use L1S1A0-L1T9Z9
    Code to use Start End
    M1A1A0-M1B9Z9 M1A1A0 M1B9Z9
    T4A0A0-T4B9Z9 T4A0A0 T4B9Z9
    L1S1A0-L1T9Z9 L1S1A0 L1T9Z9
    H1A1A0-H9Z9Z9 H1A1A0 H9Z9Z9
    E0K1A0-E0K1A0 E0K1A0 E0K1A0

    Thanks

  86. Brian says:

    Very nice tip – the date range bit was exactly what I needed.

    One question – what does “–” in “SUMPRODUCT(–” do?

  87. Jessica says:

    I’m hoping this thread is still active enough to get a reply to this.

    I have a VERY CONFUSING & complicated (& frankly, annoying) spreadsheet for work with data in rows (values are a series of 1′s across a table corresponding to the number of the specific product offered to someone), that I’d like to match to another table, on another worksheet in the same workbook (also with a series of 1′s across a table). Each row would have to exactly match the series of ones in the lookup table to spit out the value associated with that series of products. I can try to attach screenshots here after work today. But any help with this in the meantime would be great. I don’t know if the SumProduct will work because of the fact that it’s a range of data that must match another range of data. I have been playing with Match, Index, & Vlookups so far, none of these have worked the way I have entered them, doesn’t mean I’m not entering it incorrectly though.

    I am going to try SUMPRODUCT later today to see if it functions correctly for what I need.

    Thanks in advance!

  88. K42 says:

    I spent days trying to figure this out in a way which would also indicate when the date was not within any if the search ranges… and then finally I found this post.

    You are a genius, Thank You!

  89. Monique says:

    I need help with a large excel document, I need data to search between dates, then match a name and then return the letter or number across row 2. Can you help if I send you my spreadsheet?

  90. Roona says:

    Hi..

    M stuck with a situation and need help..
    I have a table from which I need to look up data. My table will have duplicates but I need the lastest data. Vlookup isnt helping as it gets me the topmost / old data since the latest data is always at the bottom. Its a sheet that I need to populate on a daily basis hence I cannot filter it everyday…. Can you help me with a solution please?

    R

  91. melissa says:

    I hope someone can help me with this one:

    Data set

    Column A Column B
    PriceCode1 5.36

    Sheet 2 Data Set

    Column A Column B Column C Column E
    PriceCode1 5.02 10.01 313
    PriceCode1 10.02 50.01 314
    PriceCode1 50.02 100.01 315

    Formula on Sheet 1:

    {=INDEX(‘Price calculation’!$E$1:$E$70,MATCH(C3&D3,’Price calculation’!$A$1:$A$70&’Price calculation’!$B$1:$B$70,1))}

    The issue I am having is that the value isn’t an exact match so it is returning line 3 with a value of 315 rather than line 1 and a value of 313. What am I doing wrong? There will be multiple price codes with the same range so one criteria has to be including the price code cell.

    • Jenny says:

      Mine is similar to this.
      Sheet 1:
      A: Item #
      B: Cost
      C: Effective Start Date
      D: End Date
      There are multiple rows for each item due to the cost changing at different dates.

      Sheet 2:
      A: Sales Order #
      B: Item #
      C: Sale Price
      D: Order Date

      I want to pull the effective cost from Sheet 1 and match it to Sheet 2 based on Item #, Order Date, and the Effective Date at time of Sale.

  92. Steve from Delaware says:

    I’m not sure I didn’t miss this comment in all the excellent additions to Chandoo’s imaginative formula (which worked wonderfully for an application I was struggling with). But did anyone suggest naming the two columns of data with range names? I used BOTTOMVALUE and TOPVALUE for mine, knowing that if others see my worksheet, they’ll think of different range sets. All I have to do is insert the appropriate number of rows in each of the columns (simultaneously, in fact) and the formula updates itself.

  93. Michelle says:

    I have a similar (maybe?) problem. I have a model that shows each employee going down column A and months going across the remaining columns. There’s a blank if the employee hasn’t yet started working, and a % value if he/she has started. In another sheet, I want to show the cost of property (computers) purchased per month. I’m only going to purchase a computer in the FIRST month an employee starts working. I don’t want to use a lookup because it would hit me iwht the computer fee in EACH month the employee is working (e.g., each month the employee is working will have a % value from 1-100 in the column for that month). I need to know the month where there’s a % value and all the values to the left are null and find how many times that happens in a given month to show how many employees were onboarded in a given month. Any ideas?

    • Steve says:

      Hi Michelle:
      I have a sample worksheet to share, but, Chandoo, I don’t know if we can do that in this comments section. Can we?

      I’ll describe it verbally:
      2 sheets
      One is an employee sheet with a unique value in column A like employee number.
      Column 2 has the name
      Column 3 has the start date
      This table has a name EMPNUM, so any new entries must be inserted as a row within the other rows. Easiest way is to copy the cells in an existing row, INSERT COPIED CELLS at that same point and change the values to fit the new employee listing.

      The other sheet is labeled “Percent”
      It has a grid of employee numbers down column A and twelve columns, each headed by a m/dd/yyyy from 1/1/2014 to 12/1/2014

      In each cell of this grid, you’ll see the following formula:
      =IF(MONTH(VLOOKUP($A2,EMPNUM,3,FALSE))MONTH(B$1),””,1-MONTH(B$1)/12)

      The formula simply derives the start date by looking up the employee number and returning the third column’s month value (e.g., 6/17/2014=6). It then compares that to the month value at the head of the cell’s column. If they aren’t the same, the result is NULL, if they are, the formula subtracts the percentage of the year gone by from 1.

      The count formula goes outside the range of employee rows wherever it most makes sense to you. It simply reads: =COUNTIF(D2:D6,””&”*”), which says count everything, then subtract all those that have null in them.

      There are some tweaks one could add, but this is the essence. One key is the mixed cell addressing. Each row lookup has a fixed column ($A2) and each column lookup has a fixed row (B$1).

Leave a Reply