Old tool people online ……

**Requirement specification**： Recently, there are activities in the fruit shop , Buy the same fruit at one time , The unit price is different under different weight , Like buying mangoes ,5 Jin ( Include ） following 6 element / Per Jin ,5 To 10 Jin , The unit price is 5.6, The more you buy , The lower the unit price ！

Now we need to get the corresponding price according to the weight the customer purchased , Calculate the amount ！

The difficulty of this requirement , You need to find the name of the fruit , Also need to match according to the interval ！ Many students , Know the exact match , direct VLOOKUP+MATCH that will do , But this is an interval and I don't know how to deal with it ！

The point is how to find the corresponding fruit 、 Unit price under the corresponding weight , In fact, it's relatively simple ！ Let's first write the formula according to the original setting , Then read and optimize ！

▼ Please read me , I'm just a humble formula ……

=VLOOKUP(B2,$F$2:$K$7,IFERROR(MATCH(C2,$G$1:$K$1)+2,2),0)

The core of this is still MATCH function ,MATCH The function has three matching patterns depending on the third parameter , The most used is exact matching , It's a perfect match , Generally, the third parameter is 0 Or just write a comma

Although it has little to do with this formula , But the frequency is high , Let's talk about ！

**MATCH Exactly match - The third parameter 0 Or shorthand （ Just write commas ）**

Return the search value , In the search area

**For the first time**Position of appearance ！ If you can't find it, you will report an error

This is the most common usage ！ This time the demand obviously does not need this kind of precise match ！

**MATCH Ascending search pattern **

Here we have a look at the official explanation of the third parameter ：

In this case, we use 1 Or omit ！ Let's read about it

Two main points ：

1、 The second parameter must be in ascending order , This case fully meets the requirements

2、 Returns the maximum value less than or equal to the lookup value .

For the second point , Let's add , For example, we look for 10, stay {1,3,5,10,20} In interval

First find a ratio less than or equal to 10 Of Yes {1,3,5,10} , The maximum value of this is 10, So back 10 Corresponding position .

Convenient interpretation , Let's paste the formula again ：

Why +2,MATCH Matching is from G At the beginning of the column , That is, if it can match to the minimum, it is 1

And we VLOOKUP It's from F Column names start with , first 5 The price corresponds to 2, So we should add 1 Do you ？

It's not true , There is also a pit here , Namely 5 Questions below yuan , It's not reflected in the table , It's not listed , The first one on the list should be 0,IFERROR Appearance , That is to deal with this problem ！ The correct interval setting should be ！ In itself 100 I don't think about it ！ exceed 100 Jin will have problems ！

If you think about fault tolerance , We should put online 100 Think about it, too , such as 100 More than one kilogram 1 element / Jin

▼ Please read me , I'm just a humble formula ……

=VLOOKUP(B2,$F$2:$K$7,MATCH(C2,$G$1:$L$1)+1,0)

So we don't have to think about fault tolerance ！

If you're a little bit smart , Mastered MATCH+1 Medium +1 Or not ！

▼ Please read me , I'm just a humble formula ……

=VLOOKUP(B2,$F$2:$K$7,MATCH(C2,$F$1:$L$1),0)

MATCH Returns the position of the second parameter that satisfies the condition for the first time , We just need to move forward one , So the first thing that satisfies the condition is 2,VLOOKUP The search itself is No

**1**Column , So the unit price you need is from

**2**Column start , Dynamic Fit ！

as for MATCH For the third time -1, It should be clear from the instructions ！ If it's disorder, do you know how to play ？

There are both unit price and quantity , As for how much money was made in the end , Miss said that she would …… Tool person offline ……！