On a day-to-day basis, there are a lot of finding matching problems, for example, we want the results to be changeable
When we select the department in the drop-down menu, we get the result of the corresponding department
When you select Salary from our drop-down menu, you will get the corresponding salary
We can use the VLOOKUP formula + MATCH combination to quickly complete the calculation
1: VLOOKUP formula
When we look up the result by gender, we enter the formula:
=VLOOKUP(G2,A:E,2,0)
When the search result is a department, our formula needs to become:
=VLOOKUP(G2,A:E,3,0)
This is the third parameter of the VLOOKUP formula, which will change depending on the result to be found
2. MATCH formula
Is there any formula that can be calculated, and when looking for a change in value, calculate the required result column
There is a MATCH formula that solves it
When we enter the formula is:
=MATCH(H1,1:1,0)
It represents the value of cell H1, which is searched in the first row, and 0 is the exact search
It will be able to find that in the first row, it appears in the third result
When we change the value of cell H1 to seniority, the result of its calculation is 4
3、VLOOKUP和MATCH组合
Therefore, the value of cell I1 can automatically calculate which column your demand result is in
We only need to fix the 3rd parameter of the VLOOKUP formula to cell I1 and get the result:
=VLOOKUP(G2,A:E,$I$1,0)
If you say, you don't want to use helper cells for calculations
Then we can use a combination of two formulas to do the math
The formula used is:
=VLOOKUP(G2,A:E,MATCH($H$1,$1:$1,0),0)
With the modification of the H1 cell, the search result can also be automatically calculated:
Have you learned this little trick? Try it out!