Excel finds and returns the last same value

Source: Internet
Author: User
Tags range

In Excel, we often use the VLOOKUP function to find the same data, you can find across the worksheet, but also across the work and find, very convenient!

Still, the problem is that the VLOOKUP function can only find the first value of the same value. If we want to find the last value of the same value, we can not use the function, we have to use the following methods, please read carefully, may be helpful to you.

Below, the function we use is the lookup function.

Let's look at the table first.

The following figure, in Sheet2, we hold two columns of data, A is the operator's number, and B is the operator's date of operation.

Now, we're going to analyze the problem, and each operator is logged in chronological order, and there are duplicates for each operator's operation number.

The problem is that each operator can be repetitive, how to quickly indicate the last operation time of each operator?

The method is very simple, and now we use Sheet1 to find the last time that the operator is repeated in the Sheet2.

Computer Tutorials

In column A of Sheet1, as long as we give an operator number, then, in the corresponding column B, we get the last operation date that the operator number corresponds to in Sheet2.

For example, in B2, use the formula: =lookup (1,0/sheet2! \$A \$: \$A \$18=a2), sheet2! \$B \$: \$B \$18)

You can draw the results, and other use of the filler tool to pull down on it.

Knowledge Development: A description of the formula

1,0/(sheet2! \$A \$: \$A \$18=a2), sheet2! \$B \$ \$B \$18), translated to Chinese, should be:

Lookup (1,0/(data lookup range = condition), the range of data that needs to be returned after the condition is met)

Related Keywords:
Related Article E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD