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,
- XLOOKUP 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
We can use XMATCH:
Since we just want to know which row will contain the value, we can use XMATCH as shown below.
=XMATCH(1, (B6:B15<=C3)*(C6:C15>=C3))
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.
How this works:
- C3 contains our lookup value
- B6:B15 has the lower boundary
- C6:C15 has the higher boundary
- The (B6:B15<=C3)*(C6:C15>=C3) returns a bunch of 1s or 0s. It will be 1 whenever C3 is between column B&C values and 0 otherwise.
- XMATCH will match the first 1, ie the first row that matches the range.
Or even the SUMPRODUCT
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:B15andC6:C15respectively,
We write,
=SUMPRODUCT((B6:B15<=C3)*(C6:C15>=C3),ROW(B6:B15))-5
There are 3 portions in that formula,
(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 1ROW(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-5part: We reduce the output value by 5 since our value began in row 6, not row 1.
Use this to lookup date ranges too:
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.
Click here to download the sample workbook.
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 XMATCH (or 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 🙂
11 Responses to “Fix Incorrect Percentages with this Paste-Special Trick”
I've just taught yesterday to a colleague of mine how to convert amounts in local currency into another by pasting special the ROE.
great thing to know !!!
Chandoo - this is such a great trick and helps save time. If you don't use this shortcut, you have to take can create a formula where =(ref cell /100), copy that all the way down, covert it to a percentage and then copy/paste values to the original column. This does it all much faster. Nice job!
I was just asking peers yesterday if anyone know if an easy way to do this, I've been editing each cell and adding a % manually vs setting the cell to Percentage for months and just finally reached my wits end. What perfect timing! Thanks, great tip!
If it's just appearance you care about, another alternative is to use this custom number format:
0"%"
By adding the percent sign in quotes, it gets treated as text and won't do what you warned about here: "You can not just format the cells to % format either, excel shows 23 as 2300% then."
Dear Jon S. You are the reason I love the internet. 3 year old comments making my life easier.
Thank you.
Here is a quicker protocol.
Enter 10000% into the extra cell, copy this cell, select the range you need to convert to percentages, and use paste special > divide. Since the Paste > All option is selected, it not only divides by 10000% (i.e. 100), it also applies the % format to the cells being pasted on.
@Martin: That is another very good use of Divide / Multiply operations.
@Tony, @Jody: Thank you 🙂
@Jon S: Good one...
@Jon... now why didnt I think of that.. Excellent
Thank You so much. it is really helped me.
Big help...Thanks
Thanks. That really saved me a lot of time!
Is Show Formulas is turned on in the Formula Ribbon, it will stay in decimal form until that is turned off. Drove me batty for an hour until I just figured it out.