Excel has multiple values that meet the conditions, how do I return the values corresponding to othe

This function can be done with Excel 2016 or later versions of Power Query.

First, divide all tasks from Monday to Friday into a single column, and the separator is "+" in the separator diagram

Even if no one person is doing more than one task on a given day, do this because someone may do it in the future. The following picture will look like this.

Select all lines except the name and do a reverse perspective.

The results are shown in the figure below.

Let's put Monday. 1 or something. 1，.2 delete, also with a split, the separator is ".".“

Put the redundant attributes. 2 columns removed.

Another operation that will definitely go wrong, according to the task and date, sum the name, because there may be multiple people in the same task, to merge, but ordinary users will not enter commands, just use this way to change the easiest.

A little more modification, it turned out to be.

Modified to: <>

Column 1],","), pay attention to capitalization.

If such a ** can meet the requirements, you can not continue.

If you want to pivot again, select the attribute (date), pivot, the value is count (name) and then have a special operation, be sure to select "do not aggregate".

Then change the name of each column, and then upload the result, if you want to put it in the specified position, use "Upload to......”

The final result is as follows.

In the future, modify Table 1 and refresh Table 2.

According to the title, the formula is set as follows:

vlookup("*j2&"*if(,$c$2:$c$4&$d$2:$d$4&$e$2:$e$4&$f$2:$f$4,$b$2:$b$4),2,)

=vlookup("*j2&"*if(,$c$2:$c$4&$d$2:$d$4&$e$2:$e$4&$f$2:$f$4,$g$2:$g$4),2,)

=iferror(index(emitted time column mark, min(if(mixed reference of numbered area - row absolute = number to find, row($numbered area start line number: $numbered area end line number), 4 10))) has not been issued")

When performing row and column crossover data search according to multiple conditions, how to quickly locate and find references to improve our work efficiency?

<> array formula, press Ctrl+Shift+Enter to confirm.

e3 input = v lookup (b$1 and a$3, if (, Table 2!) A: And Table 2! b:b, table:c), hold down the Ctrl key and hold down the shift key.

The implementation method and detailed operation steps are as follows:

1. In the first step, open the ** to be processed, fill in the score and grade, as shown in the figure below, and then enter the next step.

2. Secondly, after finishing the above steps, in cell B2, enter the formula pointed to by the following arrows as below screenshot shown, and then go to the next step.

3. Then, after completing the above steps, press the Enter key to get the result, as shown in the figure below, and then proceed to the next step.

4. Finally, after completing the above steps, you will get the desired result, as shown in the figure below. In this way, the problem is solved.

The title is not clear enough, so I'll give you an estimate of it, and if it doesn't match the meaning, I'll talk about it.

Let's be sure that this can be made, but it's troublesome. And then there's the question about this data. In my understanding, this should have been used for analysis, but it is not standardized at all to make it like this, so it is recommended to re-learn.

I made a semi-finished formula, you can use it first, and it is estimated that it will not be used for a long time.

f22=textjoin("/true,iferror(index(c3:c19,if(iferror(find(d3:d19,textjoin("/true,a22:

d29))*find(e3:e19,textjoin("/true,a22:d29))*find(f3:

f19,textjoin("/true,a22:d29))*find(g3:g19,textjoin("/true,a22:

d29))*1,1)=1,""row(a1:a17)))

If you have a question, ask it. There are some parameters in it that you need to debug directly, because I don't know how big you plan to make this table.

with a custom function.

1. Open the ** that needs to be processed: you need to fill in the score level.

2. Enter the following formula in cell B2 before the bridge "hail elimination = if(a2<60,"Fail",if(a2<70,"Pass",if(a2<80,"Medium",if(a2<90,"Good",if(a2<=100,"Excellent"))

3. Press enter to get the result.

In Excel, if you want to return a certain value if you meet multiple conditions, and if you don't meet another value, the specific method and operation steps are as follows.

For example, if the condition value is 6 and the condition value is 8, the specific steps are as follows:

1. Find the Excel** document that needs to be operated on the computer, as shown in the figure below, and enter the next step.

2. For example, enter the data to be judged in column B, as shown in the figure below, and then enter the next step.

3. In the cell that needs to return the value, enter the state, and then enter the formula =if(or((b4>=3000)*(b4<=3500),(b4>=4000)*(b4<=5000),(b4>=5900)*(b4<=6300)),6,8), as shown in the following figure, to enter the next step.

4. After entering the if formula, you can see that the return value has been automatically entered into the cell position, as shown in the figure below, and enter the next step.

5. Then pull down the judgment result cell, you can get the judgment result value of several other numbers, as shown in the figure below, to complete all operations.

1. First of all, open the excel data table, as shown in the figure below, and filter out the cells containing "dragon" in column A.

2. Select cell A1, and click "Filter" in "Data" as shown in the figure below.

3. Click the drop-down button, select "Text Filter", and continue to select "Contain", as shown in the figure below.

4. Enter the characters you want to find, as shown in the figure below, and click "OK".

5. In this way, all the cells containing the word "dragon" in column A were found out by filtering, and the result is shown in the figure below.

1. First use the and function.

to indicate that multiple conditions are met at the same time.

2. If the text of column A is "A", and the data of column B is greater than 210, mark "Y" in column C, and enter the formula =if(and(a2="a",B2>210),"Y"," in C2

3. Each condition and criterion is judged to be equal, if it is equal to return true, otherwise the rollover returns false, only all conditions and judgments are patiently returned to true, that is, when all conditions are met, the and function will return true.

4. Then use the OR function.

To illustrate, as long as one or more of the multiple conditions are met.

5. If the text of column A is "A", or the data of column B is greater than 150, mark "Y" in column C.