# PROCV Function Variations in Excel: More than One Condition and Repeated Values

190
73911

PROCV is one of the best search functions that Excel provides for its users. I say this because it is not very complicated to understand its operation and there are several tutorials on the internet how to use the function. We have already written a very complete post about how to use PROCV (VLOOKUP).

If you are one of those who prefer to see the video explanation, we have a module on the PROCV function in our basic excel online course. If you already know PROCV and want to know some more advanced functions like the INDEX (which we will talk about more along this post), I recommend our Advanced Excel course.

## 2 Possible Problems with PROCV

There are two issues that can make it difficult to use this feature:

• 1. When you have more than one condition

In the example above, we have two conditions, make and type of vehicle. If we wanted to use PROCV without any other function to help find the base value in the value specification table we would have problems because we would either use the brand column or the vehicle type column.

• 2. When you want to see all possible results

In that case we have a classic problem, which is when you want to see all the sales figures performed, but the PROCV would only return you a value and in that case we have a minimum of 2 values ​​per option.

Let's now see how to solve each of these problems and what you need to use to get the best result.

## 1. Example of PROCV with more than one condition

Like everything else in Excel, you can always think of logic using other functions to solve your problem. Therefore, let us first understand the problem that we are facing here understanding the concept of PROCV. See its syntax:

= PROCV (search_value; table_frame; index_num_column; interval)

• The problem:

The value sought is a unique value and in our case we want to look at two different values: brand and type of vehicle

We would not be able to use the G4 and G5 cells at the same time at the same time.

• The solution:

Find a function that transforms these 2 values ​​into a single value. As we know, the CONCATENATE function is used to group strings. If you want to remember more, see our post with 5 applications from CONCATENAR. When using it in G4 and G5 cells, we would have something like:

See I've put a hyphen in the concatenation to have a more friendly text. Without it, it would be IvecoFurgão, which could confuse a user who did not understand the purpose of it.

• What would be the result:

Now, to get the result correctly, you need to create one more column in the database table, to have the same result type. Look:

If the extra column does not please you, you can hide it without any problem. Now it is only necessary to use PROCV, using as search value the cell that we add and the matrix that looks for the values ​​in columns D and E, see in the formula:

Ready, the function works perfectly and you can use up to more than two speakers if you want. You can also use CONCATENAR directly in the function, see:

## 2. VLOOKUP example in a list with repeated values

In our second case we no longer have the problem of searching 2 different types of values, but we have a single list with repeated values:

See that in our list of sellers we have the Jarbas (2 repetitions), the Josias (4 repetitions) and the Jonas (3 repetitions). Trying to look for a historical sales of each of them we will have problems.

• The problem:

Note that when you use PROCV it will always fetch the first value_looked for Jarbas, and when it does, it will return to the second or third column, depending on what you are looking for. It turns out that when we take this formula down to try to see the other sales it does not work, it keeps returning the same values.

• The solution:

We can use the INDEX function together with others (MIN, SE and LIN) to create a matrix function that searches all occurrences for the searched value.

• What would be the result:

To get the result correctly you need to use the following function:

{=SEERRO(ÍNDICE(\$B\$4:\$D\$12;MENOR(SE(\$B\$4:\$B\$12=\$G\$4;LIN(\$B\$4:\$B\$12)-3);LIN(A1));2);””)}

Note that the function has braces {} before it starts and at the end, this indicates that it is a matrix function. To make your function in a matrix function simply press CTRL + SHIFT + ENTER after typing it completely. If we changed the name of the seller in our list we would have a new result:

### Step by step use of functions in this matrix formula:

Since we have a very complex function, I'll show you the step by step so that you understand each factor explained.

• Step 1 - SE with LIN

To begin with, the first function we will use is the SE with LIN

=SE(\$B\$4:\$B\$12=\$G\$4;LIN(\$B\$4:\$B\$12)-3)

Basically I'm asking Excel to parse the B line between the 4 and 12 cells (which is where I have the sales - it could be bigger if I had more sales). Every time it finds a result equal to the value of G4 (seller's name), it will return the row of that result.

In our case above would be the lines 5, 8, 11 and 12. Now I subtract 3 from these values ​​so I simulate that my list starts on the line. So, I would have the values ​​2, 5, 8 and 9.

• Step 2 - MINOR

Now I want to tell Excel that from the values ​​it has found, that it returns me the smallest

=MENOR(SE(\$B\$4:\$B\$12=\$G\$4;LIN(\$B\$4:\$B\$12)-3);LIN(A1))

Note that the only part of the formula with relative reference is LIN (A1). This happens on purpose so that when we drag the formula down, it returns the second, third and fourth smaller respectively. So we were able to place the sales order correctly. If we marked another seller, we would only have the sales made by him numbered in the correct order:

As Jonas only performed 3 sales, a #NUM! Error appears! at the place of sale 4. Anyway, we still can not put the name of the product sold, just your reference. To get this result we will use the INDEX function ...

As we said in the beginning, this is the main function to arrive at this result, since it makes searches in a certain list from imputed values. In this way, we will search the table B4: D12 by looking for the row reference obtained by the smaller function and always analyzing the value of the second column (vehicle type) of our initial reference table:

Thus we transform the numbers into values ​​and we are already with the desired result. Now you just need to adjust the formula so that no errors appear and then replicate to the next column to get the sales figures.

• Step 4 - SEERRO

This step is quite simple and without mystery. Just repeat the function used by putting the SEERRO in front and leaving a "" (blank value) at the end. Remembering that the function is matrix, from the beginning, this means that every time we move the formula box we have to use CTRL + SHIFT + ENTER.

Also remember to use the fill handle to drag the formulas down and not have to do one by one.

So we'll disappear with the error value. Understand, it's still there, only it gets disguised as blank value so your spreadsheet gets friendlier to anyone viewing.

• Step 5 - Replicate the formula to fetch the base values

Finally, if we want to do the same function in the column on the side, just copy and paste. Obviously we will have to make a change: change the value 2 of the INDEX function to the value 3. We will do this, because now we want to search for the base values ​​(column D) of our auxiliary table.

Okay, that would be the function of column D:

{=SEERRO(ÍNDICE(\$B\$4:\$D\$12;MENOR(SE(\$B\$4:\$B\$12=\$G\$4;LIN(\$B\$4:\$B\$12)-3);LIN(A1));2);””)}

## How do you do?

If you have enjoyed the possibilities of PROCV and the INDEX function and want to learn a little more about them, try to know our Advanced Excel course.

If you use other functions or ways to get these results, tell us in the comments.

Are you in doubt? Get in on LUZ Excel Forum and send your question!

previous article10 Basic and Advanced Excel Exercises
Next articleHow to do sales projection
Administrator and turismologist, passionate about the possibilities that Excel gives to managers, entrepreneurs and students. He is the managing partner of the spreadsheet department at LUZ - Spreadsheets (luz.vc) and is responsible for the development of spreadsheets with business and personal focus commercialized in its website. He also teaches online Excel courses (cursos.luz.vc) and likes to help thousands of planners through the LUZ Forum and Blog.

1. Rafael, thanks for the reply, but I've already been able to solve the problem. The issue is that I was doing CTRL + SHIFT + ENTER for all cells and then the A1 cell was always the same on all other cells. I realized that it is necessary to just CTRL + SHIFT + ENTER in the first cell and then only drag to the cells below.
Thanks again for your help.

2. Hi Everton, you can create auxiliary columns (which will not change the order of the worksheet, but will replicate this information) and then use PROCV. If you really do not want to follow this path, you can use DESLOC, but it's more complicated

3. Hi Nuno, by any chance this A1 is formatted with \$? \$ A \$ 1? If yes, this is what hangs, if not, I would not know what might be happening, since when dragging a formula it varies the reference if you do not have the \$

4. In my spreadsheet everything is working, except the LIN (A1) question. When I prime CTRL + SHIFT + ENTER it always stays with LIN (A1) on all rows, ie the lowest value is always appearing and does not update to the second lower value and so on.

5. How do I query a table for all the items on a given row, using the last (value / name) of a row ... or any other value as a reference. I can only do this when I put it to search for the first one ( name / value) of a line.
EX:
Activity / Quant. / Date / Responsible.
exchange lamp / 2 / 01 / 08 / electrical

I can only make the whole line appear if I discriminate the activity, however I would like to do this by searching for the person in charge, without having to change the order of the worksheet.
If you can help me, I'll be most grateful.
That,

6. Hi Jose, in this case you need to create a routine in VBA so that you paste the items as values ​​(not formulas), so you have a picture and not a dynamic formula that changes as you change the values ​​of the products

7. Good day.
I'm making a spreadsheet where there is a sales control and the value of the product is fetched from a stock table, the problem is that your I change the value of the product in the stock, it changes the value of the products that were already sold in the sales table.

HELP! (I.e.

8. Hi Marcos, I'm not sure how to help. I find it odd to have this limitation up the 1400 line, but since I've never come across this kind of error, I could not tell what it could be. My tip is to parse the 1401 formula to understand why it does not work

9. Good morning, Rafael.
My database has 1460 rows, however I noticed that the formula only works up to 1400 rows.
Is there any way around this? I'm working with Excel 2016.
Att,

10. Congratulations on the page. I searched through many sites for this solution to find more than one record repeated, and your example killed the face riddle. Very good. Thank you!

11. Hi, I managed to fix it already… I was putting the brackets and pressing the ctrl shift enter, then they told me that I didn't have to put the brackets on, that it would appear alone, now, it was perfect! thanks !!! =)

12. Hello, I did everything beautiful and wonderful and when I press CTRL SHIFT ENTER the formula does not work, the formula itself appears instead of the result, I tried other formulas from that normal wheel, the problem is in generating the same matrix.
I am trying to do the following:
Plan1 = FY17
Plan2 = COLLABORATORS
in FY17 E7 I have the Cost Center field and I would like the formula to find all the people in COLLABORATORS columns A and B who have the same cost center as FY17 E7 and return those people to the list in FY17 in column B from line 10.
I am using the formula like this: {= BUG (INDEX (COLLABORATORS! \$ A \$ 2: \$ B \$ 2000; MINOR (IF (COLLABORATORS! \$ A \$ 2: \$ A \$ 2000 = \$ E \$ 7; LIN (COLLABORATORS! \$ A \$ 2: \$ A \$ 2000) -1); LIN (A1)); 2); ””)}

* When I remove the {} the formula “works”, but it returns people from the next CC to what I'm looking for….

13. It's Milena, if it did not work, I'm not sure how it could be the best use for this particular case. If any developments or new doubts arise, just tell me that I try to help

14. Hi Maicon, I do not know if I fully understood the explanation, but I think it is possible to do some tests:
1 - try to use the full function
2 - instead of = CN8, put the same value in another cell and use this new reference
3 - the function must be matrix

15. Hello, can you help me?

I am using only this step MINOR (IF (\$ CN \$ 6: \$ CN \$ 8 = \$ CN \$ 8; LIN (\$ CN \$ 6: \$ CNN 8) -5); LIN (A1))
In summary, in this table I have only one data that is equal to this list of CN column, so it should bring the result of this line, right?

I started in -5 because the data in my table starts the 6 line (5 = caption)

But the formula is wrong. I've tried turning it into matrix, but it still gives the error #VALUE.
If I use only this step works?

Excel is justifying that this function cannot be used in this way “IF (\$ CN \$ 6: \$ CN \$ 8 = \$ CN \$ 8”
That I can not use a larger amount of rows in the column to compare with CN8.

16. Hi Darlon, thanks for the compliment. As for your doubt, the LIN (A1) function returns the 1 value, A2 returns 2, and A243 returns the 243 value, it's just a way to let the variation of dynamic downlines

17. Hello .. Good evening
Firstly I have to congratulate you on the excellent explanatory written, very good and the narrative very clear.
My question is as follows, in the minor function on the "lin" I would like to know the reason for having placed the cell "A1" because I was having problems with this, but from the moment I left the parentheses blank the function worked. Before that, I was always repeating the same value.
Thank you in advance for your attention, thank you.

18. Hi David, when I answered the supplement of your question I had not read this yet. It seems simple to solve this problem using CONT.SE or SOMASE according to the specified column or date

19. Hi David, in that case, maybe using PROCH or changing the positioning of the INDEX arguments

20. Good evening Rafael:
I have a hard time finding a formula that does the following operation;
look up the contents of the A3 column in the D3 column: d50 and add the values ​​contained in column G of the same rows contained in the column D3: D50.
It is a table of invoices.
I have the same product and NF differentiated and I need to put in the worksheet the values ​​of each item and reference the invoice number. At the end I need to see the value of each invoice.

Then: column A and D = no. NF
Column G = value per item
Column H = total value of NF.

There is some formula that performs this operation. I would not like to use SOMASE because I would have to change every time I enter a new NF number. I need him to recognize this new number and do the math.

Thankful,

21. It would be a formula similar to the one you taught us, but looking for values ​​in columns rather than lines.

22. Rafael good afternoon ...
The most difficult thing is to explain my need, I will use simple terms and basic values ​​if it is not clear, can I send a "print"? but let's go.
In column [B] I have dates from cell B2, in row 1 I have several materials that were sold (I will use fruit) they are fixed and do not repeat… (apple [C1], banana [D1], pineapple [E1] approximately 50 items) ...
During the month I was launching my sales and in my database the dates will be repeated several times (ex row 5 to 15 are referring to day 01 / 02, line 10 to 30 are day data 02 / 02 and etc.) now I need to demonstrate which 5 best sellers products on the specified day.
I tried to adjust the formula above, however I was not successful, maybe I need to use COL instead of LIN ... I don't know. Would you help me. For understanding can I send a print?

23. Hi Italo, you can use a CONT.SE to count the number of times the number appears and, if that number is greater than 3, use conditional formatting to modify the value.

24. Hello @Rafael Avila could help me with a spreadsheet, I have a list of employees where they borrow items I would like to know how to do if the name of the employee appears more than 3 times highlight his name with Bold and red for example, since already thank you .

25. Hi Gabriel, do the whole function and make sure all points are being met remembering that the function is matrix

26. In this case, LIN (A1) returns the value 1, it could be B1, C1, etc. - so the possible variation in this case is for another column in the same row

27. Hi Leonildo, in this case you need a formula to find this value (if it exists). Maybe PROCV

28. Rafael, can you help me in a spreadsheet, I want to have a result in that when entering a value and this value equals a value of one of the column rows it gives me the percentage. Eg if I type 1,7 and it finds in that column this value, then multiply by the next cell that will have a value corresponding to that line.

29. Good morning Rafael, this article is very good, but I was left with a question, what is the reference for the LIN (A1) function, I have to put A1 as a reference, or it can be another reference, I did not understand that part.

Thank you very much in advance!

30. Rafael, great tutorial, the second example is exactly what I need to do in a spreadsheet at work, but right in the first part where we set up the formula “SE with LIN”, my excel returns the value “FALSE”, even if the name of the seller I am looking for is within the established range for checking the SE condition. Can you help me, please? Thanks!

31. Sensational Rafael! It took my doubts heavier, so to speak.
I have two spreadsheets, the first one is my database and the second one is just for me to pull the most important information from the first worksheet.
In the first, there is "classification" (whether it is expense or revenue), "cost center" and "value".
In the second worksheet, I used the formula: = SE (PROCV (F5; Jun! I7: K8; 3; FALSE); SOMASE (Jun! I7: K8; F5; Jun! K7: K8)) to bring me the sum of products that appear more than once.

How do I get him to bring me the sum of the receipts and then the sum of the expenses, separately?

32. Hi Alessandro, to do this I think it is best to use the matrix formula that I explain in this post - I'm not sure how to help, because the details of the formula are already here - try to apply by making the change to your table and spreadsheet - if have doubts during use, send me here and try to help

33. Good afternoon!

I need to pull the information from a "Sales" tab containing the product name, unit value and quantity sold to another "Results" tab where it brings me information only about the products I sold and quantity, because I don't always see the same products and the same qty, day to day varies a lot. I thought about using the qdte column if it is 0 not to bring the product to the “Result” tab, but I don't know how to use these formulas. Can you help me?

34. Excellent publication! I was able to do exactly what I wanted, I was working on an 2-like case. Congratulations!

35. Excellent Etieny! This is our goal, to help planners and planters on duty =]

36. Rafael, thank you so much !!! Your examples gave me a light to solve my problem! <3
I had already tried BDMIN / BDMAX, I was already considering appealing to the macro…
Thanks so much for the content!

37. Hi Thalita, you need to understand how your table is mounted, you're probably putting some wrong argument

38. Good morning,

I was unable to return the values ​​of the lines, he always returns the value "1", and does not recognize the other names of Jonas and Jarbas, in these cases he returns "false".

39. Hi Willian, I do not know if I understood correctly, but in general you can do a CONT.SE for the entire array (all columns)

40. Good afternoon, Rafael!
My congratulations on the work, I wanted to clarify a question, I have a table with different amounts of data and many times I have the same individual in column A in row 1 and in column B but in row 3, I wanted to know how to do it “ search ”the values ​​of the same individual in all columns.
Since in the table there is a column of species (which are the individuals) and the values ​​of each individual in the following table… and so on, but as there are different amounts of individual, the same individual changes “position” leaving line 1 and going to line 2 for example, how can I solve this?
Hug!

41. Hi Gaby, -3 is used because the table used in the formula starts on the 4 line. If you start using the table in the 8 line, you would use -7

42. Hi, Evancleide, is everything okay? I'm not sure how best to do this without being an auxiliary column. Maybe using count or sum functions with more than one condition can be valid (CONT.SES and SOMASES)

43. Good afternoon, I'd like you to explain to me better why -3 after the LIN function. I can not continue because I do not understand this function.

44. Thanks for the feedback, guys.
On the auxiliary column: I wanted to see if there is another possibility, because, I think that creating another column would slow the worksheet. The amount of records is too large.
I use the formula below, found on the net, which compares only one column. Ex.:
SOMARPRODUTO((D3:D30000″”)/CONT.SE(D3:D30000;D3:D30000&””))
I needed a formula in addition to the formula above (which compares only in one column), check a second column, which would be work orders. For example: on OS_004 = 9 single boxes; on OS_007 = 8 unique boxes. The idea is to have only one spreadsheet with a list of all boxes of all “OS” and make a summary table with the formula to know how many boxes there are for each OS.

45. It is the best option in my opinion, but it leaves a lot to be desired in large databases where the formula slows everything down, a lot of information to process without mentioning that we want more than one return result ”(in my clear case).

Congratulations great topic.

46. Hi Evancleide, I imagine you first need to create an auxiliary column and then count it (with CONT.SE) to probably know the correct number of items per box

47. Good afternoon,
I found this site and I really enjoyed the posts.
I have a need that I can not solve. Can you see if you can help me, please:
I have a document storage control where I need to control the number of boxes per OS (Service Order). There is a list of documents contained in each box / OS. Since there are multiple documents in a single box, I need to count the number of a box once per OS. I tried some formulas, but I did not get the desired result.
Example box summary by OS
Cx Box_OS_004 9
Cx Box_OS_007 8

List of documents per box / OS:
OS Document box
1467 OS_004 Doc_001
1461 OS_004 Doc_002
1467 OS_004 Doc_003
1467 OS_007 Doc_004
1469 OS_004 Doc_005
1467 OS_007 Doc_006
1467 OS_004 Doc_007
1467 OS_007 Doc_008
1467 OS_004 Doc_009
1468 OS_007 Doc_010
1467 OS_004 Doc_011
1467 OS_007 Doc_012
1467 OS_007 Doc_013
1467 OS_004 Doc_014
1467 OS_007 Doc_015
1467 OS_004 Doc_016
1467 OS_007 Doc_017

48. Hi Leandro, I believe that using PROCV you get to the result, but as column A is earlier, you would either need to either change the order of the columns in the first tab or create an extra column on the side of the Brazil column, with the same values ​​in column A. PROCV would thus function

49. If you have a tab for each month, the formula of the month will have to reference the correct tab. About summing up the values ​​of a specific branch is just using SOMASE. Example, if we have the JANUARY tab, with column A with the branch name and B with the value and, on the SUMMARY tab, you want to get the values ​​of branch A.

In that case, it would look like = SOMASE (JANUARY! \$ A \$ 1: \$ A \$ 100; ”Branch A”; JANUARY! \$ B \$ 1: \$ B \$ 100)

50. Hello good afternoon!
Great site, Congrats!
I have a question and maybe you can help me, I have a worksheet where I want to get information from a tab, example; Column A (KIT01) COLUMN B (BRASIL), AI IN ANOTHER ABA, I want in column B to have information (BRASIL), Ai in column a wanted to pull information (KIT01) from another tab. Did you get it? can you help me?
Thank you!

51. Hello,
First of all, congratulations to the website, it's wonderful.

I am making a spreadsheet and I want it to return a result based on the selection of two conditions: Month and Branch.

For example, in the worksheet of January, I put the expense for ALL the branches, but in the master worksheet, I want to be able to filter this data by Month and also by Branch.
I tried to follow the posts here on the site with Indirect, Procv, but I could not make that combination. Can you help me?

52. I did not understand the problem you're having, can you explain more?

53. In different tabs I can not export the data, only with the same tab. What would be the solution?

54. You're welcome, Luiz. I'm glad our content is helping you. If you need to get any questions, just talk. Hugs

55. I did not quite understand the structure of your spreadsheet and the necessary condition for the value, but maybe one possibility is the use of the SE function with the MINOR function, since in this way you can find the second lowest value and it is not restricted to the smallest only .

56. Thank you very much, your example was very clear and direct helped me solve my problem in a very nice way. I'll watch more content from your blog for sure.
Unfortunately I have not yet understood the functions in their entirety, such as Index, Lin and so on, but I will look at it calmly to learn how I did with ProcV, SomaSe, Summaries that I now control but that seemed to me mutants a few times behind.
Thank you so much for taking the time to share the content helped me a lot!

57. Hello, Rafael, how are you?
Very interesting to publish !!
Dude, I've got a problem, and I've researched everywhere and found nothing.
I need to look in another spreadsheet for the second smallest item with condition, so far I haven't found any formula or combination that gives me this result. With MINIMUMS () I can return the minor, but how do I return the second, third… .. minor with condition ???

58. It would be like that I used two variables but I still have the same value… I will follow your suggestion and create a variable that makes the distinction to facilitate. Thank you 🙂

59. Hi Stephany, I do not know if I understand it very well, but when I have the same values, I look for a third value that is different and use concatenated to do the searches in a single value, without having to complicate the calculations of the worksheet

60. People I have a problem and it involves having equal values… I am using the following formula: = INDEX (Returns! D \$ 2: D3171; CORRESPENSES (Remittances! F2 & Remittances! E2; Returns! F \$ 2: F3171 & Returns! E \$ 2: E3171; 0 ))
I used this instead of Procv because I need it to analyze two variables before bringing me a data. The problem is that there are lines where I have the same value and the same CNPJ, so it pulls the same result twice. The question is that I want to evaluate if there is this in another tab, in one there are two equal cases and in the other just one, I would like him to pull results only in the first and second as he has already shown that he gave # ND… Can someone help me ??

61. hi Rafael, thanks for the reply but I found out what it was! Primary problem: it was not formatted as a value… .but thanks for the help!

abs,

62. Yeah ... I wanted to show you the table, just so you understand the formatting. I wanted to use simple filter in the header for each column
Company - Country - Product - Product specification

Company and Country, the information I write only once. But the products and the specification are several (one per line). It is then that the filter does not pick up the different products, if I do not repeat the company name and the country in all the corresponding lines. Because the lines are blank, the filter understands that the company does not produce a particular product that I have not correlated with.
In fact I did not want to have to repeat 10x the name of the country, to leave the spreadsheet aesthetic and clean. But at the same time she wanted to make it functional and quick.

I'll see what I get ... even so, thank you very much for the quick response.

63. Hi Marco, without seeing what you did it is very difficult to understand why PROCV is not working. If you can specify a little more the formula or send an image here maybe give a slightly more informed opinion.

64. Hi Lisa, I do not know if I understood your problem very well. But if it is possible, I would try to use a database for when inserting certain products, already pull related information. If that is not possible, it seems to me that the work will be a little manual anyway.

65. hi Rafael, my VLOOKUP is giving me an error of # N / A but when I look for the item through “Find” (CRTL + L), he finds the item. The strangest thing is that when I search for the item in another tab (it's a list that I repeat in several tabs), it finds it! I think it's a cell formatting thing but I don't know what. Can you help me? Thanks!

66. Hi Rafael, I have a table (I think it's basic) to make, but I can't draw it. The table below describes an example of what I need. Each company is unique (not repeated); countries are diverse and are repeated. But when I specify the product for each company, the products vary in material and size. I used a column for each item / description. At first I would like to use common FILTERS (because then I go to the country, for example and I know which products are produced; or if I want to go directly to the Product and see which country has production!). However, when I generated this type of spreadsheet, I didn't want to pollute it and write it several times = COISAS LTDA. - Brazil - Dolls - Plastic - Small; COISAS LTDA.- Brazil Dolls - Plastic - Large ... etc).
I just can't filter without writing the corresponding information on each line. If I don't write everything, the Big Plastic Dolls from COISAS LTDA. do not appear in the filter !!! How can I resolve this information gap? Will I have to fill each line for each entry of different material, even if it is the same doll, in the same company? I'm going crazy, but I still feel like it's easy !!!

COISAS LTDA. - Brazil - Dolls - Plastic - Small
Grandes
Pano - Small
- Big ones
BRINQUEDOS SA - Portugal - Carts - Plastic - Small
- Big ones
- Wood - Small
- Big ones

How could I solve this 'matrix', since each row has columns that correspond and I cannot merge (for the table to be 'cute').
If you can help me, I thank you in advance.
Lisa.

67. Hi Matheus, I do not know if I understood very well what you meant. But I would try using auxiliary columns to determine what the search column would look like.

68. Hi Rafael,

In my case, I need a formula that solves the two problems mentioned above ...

I need to look up a value based on two conditions, and always return the second found value. But with the formulas that I use for searches with more than one condition (INDEX + CORRESP), I'm not able to return other found values, only the first one. Would you help me?

69. Rafael, good afternoon!

Understand. In parallel I used the formula = SOMASE (January2017! D2: D154; ”BUSINESS DIRECTORY”; January2017! E2: E153) and it worked!

Thanks a lot, thank you.

70. Hi Rafael, I did not quite understand your problem. If you can explain more, I can try to help. An important general summary is to use auxiliary tables when necessary to facilitate the type of counting or information you are seeking.

71. Rafael Avila, good afternoon!

I need help.

I have a spreadsheet with several cost centers per employee. I need to set the total cost per cost center, so far so good. However, I need to add the result of several searched values ​​and not only bring the sum of 1 collaborator.

Ex: = PROCV (search_value).
At this stage, I need the spreadsheet to add up all the values ​​for the requested values.

To get around this problem do we need to use a matrix formula? How to align it to PROCV?

Thanks for the help.

Best Regards,
Rafael Dias

72. I believe it is possible to use the LEFT function as part of the values ​​value criteria

73. Thanks, I was breaking my head to use procv twice in the same cell, it worked out what I needed. Thank you for sharing ideas and information.

74. Hi Rafael.
Based on the repeated values ​​search formula, is it possible to search for part of the text in the cell?
For example…
Tablet
Board.
Using your formula, you have to search for 3 first characters, bringing the two results with its desired column?
In short, do the research on the part of the text in the cell

75. Hi Gerson, if you want to explain a little more about your situation maybe I can help. At first, if you need to fetch the smallest value in a data series you can use the MINOR function. If this value needs to be conditioned to another, you can use auxiliary columns with the SE function to generate conditions that meet your need.

76. Good morning,

I found that this formula would save my life where I have to fetch the smallest value in a data series in a procv / index + corresp.

The point is that even if I do the same in the “same fields and values” example, it doesn't return the lowest value to me, but the first value for your criteria.

77. Hi Vinícius, I think you did almost the same as the example, but started the tables on the 3 line instead of starting on the 4 line. So you need to make an adjustment to the formula by putting -2 instead of -3. Also, it looks like you did not press CTRL + SHIFT + ENTER at the time of entering the formula. This command tells Excel that the function is dot matrix and is essential to have the correct answer.

78. Hi Filipe, the subtraction of -3 is related in which line the formula begins. As it is in the 4 line, we subtract 3 for the make sense function. On the use of A1, it is only to get a line reference that returns the 1 value, so we use the LIN

79. Great tips!
In the second variation, I did not understand the -3 subtraction and the use of A1.

80. Hi Rafael! Thanks for answering me.

Note that the columns that I need to copy are the ones marked in “green”, I made sure to mark the same columns “in green” in my control, but the Custodian sends the data of the Funds in the same spreadsheet, repeating the names of each Fund (note the column “D” from the source) and in our spreadsheet, the Funds described are available in “separate” tabs for each Fund, so in my spreadsheet there is a tab from the “Gaivota” Fund, another guide from the “Blackbird” fund, another for the “Pica-Pau” fund, etc… I need to extract the data from the source spreadsheet (where all data from all Funds are available in the same spreadsheet) and post them within each respective tab, of each Fund. Got it? So I can't just use a “VLOOKUP”, as it is limited… it will only bring me data from the first line of the Fund that I declare… I tried to create a formula with “Procv” + Indirect (pointing to a cell that informs the address / location and the file name of the custodian ...) + INDICE (in an attempt to create a declared matrix) ... But it didn't work, because INDIRETO is not working ...

Anyway, as I need to copy data from several lines (and the number of lines can change daily, due to varying numbers of operations within each Fund per day, I still haven't been able to find a smart way to get this data in the face of the daily changes in the number of lines of the files that our Custodian sends us. (I tried to standardize the sending of data in the same way that we fill our controls here, the model (customizing for our needs ...) ... then the data continues to be handled MANUALLY, being extracted from a spreadsheet and transferred to ours, in an archaic and unproductive way.

I will need to create a macro that transforms the updated data (D -1), at the end of each of these tabs, to prevent the file from becoming "heavy" ... this will be the least of the problems ... Because I'm still stuck on how to "extract" the custodian origin data ...

Sorry for the long answer!

Strong hug!!!

81. I do not understand your doubt, if I can explain a little more I can try to help (using images from your spreadsheet can make it easier)

82. Hi Helio, I do not know if I understand very well what is your problem. Can you explain a little more? What amount do you need to fetch from the custodian's spreadsheet? And what data do you want to take to your spreadsheet?

83. Can I add something else so that repeated values ​​do not appear?
Because the formula even worked but I use the part of taking duplicates it takes and then I already exchange the values ​​for the first one.

84. Hello! Very good page!

I have a problem and I would like to ask for help:

I have a spreadsheet that comes from a custodian of ours. I need to extract data from that column to another spreadsheet, but the data is repeated (in this case I can consider it a KEY!) And I need to extract ONLY the data from the 3 columns next to that key ...

Initially I tried to set up a “VLOOKUP” with OFFSET… it didn't work…
Then I tried to use a formula with INDEX and LIN… tmb didn't work…

IE: I need to extract from the first spreadsheet and insert it in the second spreadsheet, with a search parameter for repeated data (in this case, Fund names) ...

85. If a formula already exists in the cell that shows the value zero, you could use an additional IF conditional to, if the value is zero, return the empty value "".

86. Hi Fabricio, how do you want to get the date column, you can use a MAXIMUM function inside the CORRESP, so the value returned would be the largest, which means the last date inserted.

87. I do not know if I understand very well, but one possibility is to use the CONCATENATE function to get the values ​​of these three columns in the record tab that you use and do the same process in the search tab. This way you can do searches with PROCV using the 3 criteria.

88. Great post, always here accompanying.
I used the CONCATENAR more in one of the sources the value is zero, as I do so that does not appear that zero value in the cell CONCATENADA

89. I was able to bring the reflectance value using the following function:
=SEERRO(ÍNDICE(Plan1!G:G;CORRESP(A7&\$B\$3&\$B\$5;Plan1!C:C&Plan1!D:D&Plan1!E:E;0));”-“)
however it brings me the first result of the list of releases, and I need it to be the last one based on the post date column.
Thank you.

90. Hello!
I have a spreadsheet with two tabs, one I enter the painting maintenance data on the highway and the other shows a Retigraphic per km with the situation. I need the Retigraph worksheet to show me the value in the “Reflectance” field according to the last value entered and coinciding with the “Km”, “Track” and “Track” being that in these I have specific types. https://uploads.disquscdn.com/images/49ae8ce4653057ad20126798708a12b5625051c32efe2dbc7cf08d3cbb6a67ba.jpg https://uploads.disquscdn.com/images/0699cee492262aebd5a398f8368fc38213b9fa5e3c3b3e689ba49a140970a9d7.jpg https://uploads.disquscdn.com/images/a29a18beedca1f2b462bd20f8d7a8df0ee8493b51a076375c88ba8d66f0ad0b8.jpg https://uploads.disquscdn.com/images/76bf1ec94f2db249249d57146c7b5fcf1976543fddecc65c4a4edc864f7cb4c0.jpg

91. You can use the function that we explained in this post to find a list of a particular product, for example rice. Then just use a LARGER function to find out what was the last day of insertion in that auxiliary table.

92. Hi everyone, I need a lot of help!
I have a table, with various types of products, and every product input I feed into that worksheet with the date, value, quantity, product name etc.
Now I need a formula that gives the last date that each of these products watered and what was the value. Which formula do I use? I did a lot of research but I do not find it, or what I find is not right. https://uploads.disquscdn.com/images/b60e2175494a4ec9651ff4262fc2a625612b25522453ab226254375a56cb9bc8.png

93. In that case I would create auxiliary columns to try to solve your problem. For example, you can use the MAJOR function to review the employee's last vacation enjoyment date. Or do a count to know when an employee has had more than one vacation. I did not do it here to know which official and it would work, but I would go one way of using auxiliary formulas to get the value more accurately

94. Hi Yan, if cell formatting is set to stay like this the only possible error I can think of is that the searched value is not considered a number and therefore Excel does not understand that the desired formatting applies to it.

95. Hi Pedro, instead of using a numeric value, you can choose a reference cell. For example A1.

When you drag the formula to the side, it will move to get the reference B1, C1, etc. If you drag down, you'll get A2, A3, etc. Knowing this, just fill in those cells that will be used as reference with the values ​​1, 2, 3, etc. until 265

96. I created a column where I made an SE formula where when I type a certain date it puts AWAY or ACTIVE, after I created a tab in the worksheet for when I type the name and any date it searches in the history tab if on that date the employee was active or away, however there are employees who have already had several holidays enjoyment, and when I enter the name the formula procv only searches for the first value , how can I relate it to date as well? Situation example: João da Silva went on vacation in 1 / 12 / 2015 to 30 / 12 / 2015 (the date I type for example is 1 / 6 / 2016 in this case in the column I created with the formula if ACTIVE will appear 1 / 6 is not in this vacation range) Except that Joao da silva has had vacations from 1 / 6 / 16 to 30 / 6 / 16 and in the column where I inserted the formula appears on that date AWAY because I typed 1 / 6. However in the tab I created to search when typing 1 / 6 / 16 it appears that this employee is active, because PROCV searched for the first value, how can I solve this?

97. Hi Juliana, a first simpler alternative I would use is the Excel filter. This way you filter the 3 latest dates and you can update as you do reviews on new days.

If you want something more automated, I think you will have to use the above formula with some condition using the functions TODAY, TODAY -1 and TODAY -2

98. Hello, okay?
I have a spreadsheet that contains all the sales done ordered by customer code and date and I need the result to bring the latest 3 (purchase dates)

99. Is there any way to add multiple columns in the PROCV formula so when I drag the formula I do not have to add the column number? Ex: I wanted to add the 2 column before FALSE to 265. PROCV (D6; EPS! \$ A \$ 5: \$ UQ \$ 4179; 2; FALSE)

100. When I use the PROCV function the number returned does not stay with the thousands separator, even though the cell is in number format and the use 1000 separator function is enabled. What do I do?

101. Rafael, thank you for the return.
Hours later I discovered that I was simply copying the formula wrongly.
Congratulations again for the work!

102. Hi Josivan, I do not know what might be happening, because at first PROCV has no problems with the number of columns. I have already used formulas that I looked for in the 55 column, for example.

Maybe the error may be in the value you're looking for, or when you've copied the formula you may have dragged the references from the array or the search value causing the error, it's worth checking that the formula values ​​are always the same.

103. Friend, congratulations on the work!
But I'm breaking my head ... Do you have any limitations regarding the amount of data? I'm using the formula to bring up a search in a spreadsheet (over 1000 rows and about 50 columns) with many CPF's repeated. But when trying to return the results from column “I” it gives an error. The CPF's are in the first column “A” and I want you to show the results of the next columns according to the CPF's found. We find all the results of columns from "B" to "H" but when trying to bring the results from column "I" onwards it does not return any more valid results. Would you help me?

104. Hi Wagner, I looked and looked, but I could not identify where the error was. Maybe it's the -3 that I did not see in your formula. Remembering that -3 varies according to the start position of your table

105. You can use the CONCATENATE function ('; C10;' ;!) to find the value that is in C10. If that doesn't work I believe that the INDIRECT function should solve

106. How about Rafael, blz ?!
Dude, I have a spreadsheet with thousands of lines and a couple of dice. The first column contains the search values ​​and repeats multiple times, a few hundreds. In total are about 300mil lines.
But I'm interested in locating for example the first 50 records.
I took as a basis the matrix formula that you presented and adapted by replacing the data of the lines according to you presented, but when dragging the matrix function down, for example to the line 20, it works partially well. Look: it looks for the results in the order they appear (ok) but the first result appears, the next line this result repeats, the next line appears the second result, the fourth line the second repeats, the fifth line the third result and on the sixth this third is repeated and so on. Can you understand?
Here's the formula I'm using:
{=SEERRO(ÍNDICE(RI!\$A\$3:\$O\$307634;MENOR(SE(RI!\$A\$3:\$O\$307634=\$B\$1;LIN(RI!\$A\$3:\$A\$307634)-LIN(RI!\$A\$2));LIN(1:1));14);””)}
In the IR plan is where the data is and row 2 is where the titles of each column are, and row 1 I left to number each column to make it easier to find the data for each column… there are a total of 80.
Where am I going wrong?
For data volume, is this the best formula to use? I tried to identify each line repeated through the formula cont.se but excel took hours and more hours and could not process everything.
Thanks

107. Hi Rafael,
in the formula: = VLOOKUP (C7; '10'! A9: H116; 8; 0) I need that where 10 is the name of a spreadsheet also receive other values ​​placed in cell C10
In cell C10 is now 10 but can be 5, 15, 20 etc!
How can we relate this?

108. Hi Yuri, looking quickly does not seem to have any mistake. Are you using CTRL + SHIFT + ENTER to enter the function? If so, the error might be in -3, which should actually be the number of rows to the top of your table.

109. Hi Julia, in this case you will have to create some code that generates a unique value, for example a ref. of the sale. After that, you can use the CONCATENATE function to pick up the product code with that reference and remove it from the product in question.

110. Rafael,

I use two spreadsheets one for sales and one for stock. Whenever there is a sale, I inform the product code and it downloads the stock worksheet according to the quantity sold. However, if you have reported the same product in the sales spreadsheet more than once, it considers only the first one, thus getting my inventory incorrect.
Do you have any suggestions for it to output to the product code (search key)?

111. Good morning,

My matriarchal function is not able to go to the next item, it keeps repeating the same always. It works perfectly all other aspects, but does not list the next one and does not close the list. as if there were no condition for not repeating.
I created an equal table to test and I can not make it work. Is something wrong with the function?

=SEERRO(ÍNDICE(\$B\$4:\$D\$12;MENOR(SE(\$B\$4:\$B\$12=\$G\$4;LIN(\$B\$4:\$B\$12)-3);LIN(A1));2);””)

112. Hi Leonardo, I thought of you creating an auxiliary column with a conditional leave blank if the date of sale is older than the date of purchase, so only the most recent values ​​will remain. In this case, it is sufficient to use a PROCV by searching for the values ​​of that auxiliary column.

113. Hi Marcus, I believe that following the step-by-step described above is possible, yes, you are having difficulty?

114. Rafael, good morning.
I have a list of items sorted by date (from the most recent to the oldest). In my procv I want you to bring the requested information only if the date of the sale is more recent than the date of purchase, otherwise look in the next line until I find the same item with the date of the purchase prior to the date of the sale

115. Dude, I need the following:

Catch all the rows from the table that are not in the OK status and automatically feed my second table with them.

Can I do this?

116. Hi Vinícius, I did not quite understand what you meant. Can you explain a little more?

117. My brother the following my problem is to join the two possible problems in the function and I cannot concatenate with the second problem…

To better understand this problem there is josias / truck etc ..
invert the 11 line with the 12 line without changing the table above with the results

118. My suggestion would be to join the 2 features explained above. I never needed to do it, but it seems possible with a good spreadsheet organization

119. Good Morning! The tips are great, congratulations! But I would still like some additional information. I need to search with two conditions, but excel needs to return me more than a value, it needs to find the values ​​repeated. Do you have any suggestions for this?

I thank you.

120. Hi Luciano, how are you?

1 - the line function will analyze the range we want to use and the fact of having -3 is because the table starts on the fourth line. If your table starts in the first row, you don't need to do that

2 - it is probably because this is a matrix function and full of details, an alternative and simple way is to do everything in the same tab and then simply copy the values ​​to another one.

121. @rafaelvavila: very good disqus your spreadsheet, congratulations face, you manja! lol
my friend, I have two doubts:

1ª I could not understand the meaning of LIN function (A1), I would like you to explain me better
2 when I do the same thing you did, everything works perfectly, but I'm trying to work out a spreadsheet where \$ B \$ 4: \$ D \$ 12 (from your spreadsheet) is in plan2, even changing the entire formula to Plan2! \$ B \$ 4: \$ D \$ 12, I can not get it to work. Can you tell me why?

grateful

122. is almost!! srsrsr ..

Only the condition to determine the asset that is not working ...

123. Hi Raisla, it would actually look like this:

=SE(PROCV(C4;Plan4!\$I\$6:\$J\$23;2;FALSO)=”#N/D”;”Ativo”;PROCV(C4;Plan4!\$I\$6:\$J\$23;2;FALSO))

124. Hi Alfonso, I am not used to using LIbreoffice, so I would not know how to use these features there.

125. It would be like this? = IF (procv = N / A; "active"; PROCV (C4; Plan4! \$ I \$ 6: \$ J \$ 23; 2; FALSE)), because it did not work

126. Hi Raisla, just use an SE conditional before. On style:

= SE (PROCV = N / A, Active, PROCV)

Obviously you need to complete the above function, but the idea is to say that if it does not find any correct value in the first column, it will return # N / A and if you do, you have the active PROCV

127. Good Morning!

I needed help with a formula.

I needed a condition to be determined which, for example;

to look for cell A1 in spreadsheet “plan1” in range a1: b20, to return the contents of column 2, but if it didn't find cell A1 in the given range, it would return “Active”.

to find the value I did the procv, but I was unable to place a condition, in case it does not find the value to return me “active”.

Can you help me??

128. Hello. Could you do this in Libreoffice calc. I've tried a lot more with you.

129. I tried to use the “SE” and “E” function to condition the brand and section and some PROC function (PROCH AND PROC). But it did not work. I don't know if I didn't know how to use it. The following function is used: = IF (E (PANEL! A: A = ”3RHO”; PANEL! B: B = ”OIL SWITCH”); PROC (K299; PANEL! E2: K595; 1))
Being 3RHO the mark and switch the section. Then in proc it is necessary to have the conditional of the curve, to make the markup of the correct curve.

130. Good afternoon, Rafael! I have a spreadsheet, a panel of margins, where I often change the markup of a line according to the mark and section. And this markup is divided into 6 (curve A to G, according to the influence on the percentage of sale).
Whenever I make a change in a section / mark it has an effect on the A / G Curve and consequently on the items that fit that mark / section. And all these items are listed in another worksheet in the same folder (or another tab on the same worksheet, as you see fit). And in this other worksheet I have, among other information, CURVE and MARKUP of that item, based on the margin panel.
The question is: Do I need to link this worksheet to every other item so that when I change the markup of the margins panel, the item in that mark / section has its markup on the other worksheet (of items) changed automatically. Otherwise I will have to do manually or include a direct link of cells, which will require time too, and will be less flexible.
Follows images from spreadsheets.

131. Hi! I got it! This explanation was great, that's exactly what I needed! But it should be added in the explanation that the list / matrix matrix should be on the same tab otherwise the formula does not work, I wondered here to find out that this was why it was not working.

Hugs !!!

132. Hi! I got it! This explanation was great, that's exactly what I needed! But it should be added in the explanation that the list / matrix matrix should be on the same tab otherwise the formula does not work, I wondered here to find out that this was why it was not working.

Hugs !!

133. hello, I would like to know if I can find a formula to find the values, for example: I have a column with the value X and it is repeated 3 times, I need to search these 3 values, without sum… can I use this formula above?

134. Hi Luis, normally the help I give is directly from here. If you want, you can put images in the messages to help more.

However, since there are repeated values, you need to look for a criterion that does not repeat itself. For example, if you wanted to work with names and they repeat, you can think of working with values. If they have equal values, you can add a small number in it to have differentiations between one and another

135. Hi Mauricio, is everything good?

You can use the ORDER function and MAJOR to create a ranking of the best sellers. This will force you to create one or more auxiliary columns to account for everything in the correct way

136. Now I get it. I've never tried using the formula that way, but in that case you could use the same formula I showed here in the post. Only by changing the row by columns and seeing if the logic worked

137. But in my case there are several values ​​of a single reference. Ex. Carlos made sales of different values ​​and needed to return all the values ​​of Carlos' sales. In line.

__________ Sale 1 Sale 2 Sale 3
Carlos R \$ 1.000,00 R \$ 10.000,00 R \$ 5.000,00

138. Rafael, how are you? I gave one a few posts, but I still could not solve a problem in my spreadsheet.

In an ABA, I have a relationship sellers.
In another ABA I have a list of various operations that customers have generated from these vendors.

When I apply the SOMASES formula with the result I'm looking for, HOWEVER I need to CLASSify my SELLERS in GROWING order and they will alternate according to the change in values. THEN, when I do the classification the formula ends up mixing the “criterion” of the somase and shuffles the results of the sellers.

If I did not have to add the values ​​of the same client, PROCV would solve it magnificently, but as I need the sum, I am not getting the solution that I expect.

I managed to solve it, creating an extra column of VENDORS and establishing this as a “criterion” for SOMASE, leaving it out of my classification, however I want to solve it without having to create that extra column.

If you want I can send you the spreadsheet by e-mail so you can take a look.
Make the request in my email: [email protected]

Thankful!

139. And there Rafael, blz?

I'm always following the posts here on the blog and many formulas have already helped me a lot, but this time I hit an obstacle…

in a list I need to find the top 5 results, but some names repeat and values ​​too…

I would like to send you the spreadsheet so you can take a look, if possible give me your email to send you the spreadsheet.

Thank you.

140. Hi Bruno, I can help, but for this I need to understand your difficulty better. In his explanation it was not clear.

Usually when I have two conditions I use CONCATENAR to transform into a single

141. Hi Marcos. Do not mention Mateus because the VLOOKUP only finds a value corresponding to the number 13. In this case you would need to create an auxiliary column adding a minimum value (different per line) for each of the totals. Thus, it would always have different values, even if they were “equal” in the initial total

142. How to sort this table by having the names with the same punctuation show in sequence? In the example does not quote Matthew.
Usei =(PROCV(MAIOR(\$O\$17:\$O\$27;A32);\$O\$17:\$P\$27;2;0))

143. How to sort this table by having the names with the same punctuation show in sequence? In the example does not quote Matthew.
Usei =(PROCV(MAIOR(\$O\$17:\$O\$27;A32);\$O\$17:\$P\$27;2;0))

144. I need help pulling more than one different result under the same conditions.

My problem is this:

I need to pull two different results with the 2 conditional information. In a tab, there are the conditional 2 and the information I need to pull. This information is usually different. On the other tab are the same conditionals, requiring it to pull the different information. I was not able to use the formula given for 2-based conditionals, it would bring each result (if any) to the worksheet you wanted.

Note: The tabs are with the information per line. However, I need the spreadsheet to bring the information and place it by column (next to the first, second and so on).

Can you help me?

145. This article saved my life. The author is to be congratulated! Great site with great content. Thank you.

146. can you do this function horizontally?

ex. instead of looking for the data to fill in a column (column named with the reference to be searched, in this case the name “Jonas”) I need to fill the data shown in line and not column.

147. Hi Camila, for this type of search this is the only function that I know really and usually works in a good way.

I imagine some step is not being done 100% correct and so the error is being generated.

148. Yes, I understood Camilo

in which case you would need to create an auxiliary tab by looking up the values ​​by name of each worksheet and filling in it. So you could do the searches directly on this auxiliary tab without much difficulty

For you not to have much manual work, the best way to do this auxiliary tab is to use the INDIRECT function to automatically fetch the tab and cell references so that after you create the first formula, you drag to the rest of the worksheet

149. Good afternoon guys, could someone help me, I'm trying to make a spreadsheet in which, I look up a name and she gives me the sale price that I'm going to make in the month, in this case each day and a spreadsheet, so she would look for the value of each day you were a buyer and would give me the final result already added, understand? rs Thanks

150. And for what do I need, do you have any other similar function? I have tried many conditions and even transfer the data, but only always works with the last data searched.

151. I really do not know how it would work within that logic you created, but you can use the SE with the OR Yes, the syntax is always something that holds this line:

= SE (OR (1 logic, 2 logic, etc.), true value, false value)

152. Hi Camila,

from what I saw in the formula, it was apparently supposed to be working, but since this function is very complex, it might be some detail related to some of the nested functions that just looking I can not find.

153. Good afternoon. I really don't think I was very clear, I apologize. Let's take the example: in the following formula {= SEERCH (INDEX (\$ B \$ 4: \$ D \$ 12; MINOR (IF (\$ B \$ 4: \$ B \$ 12 = \$ G \$ 4; LIN (\$ B \$ 4: \$ B \$ 12)) - 3); LIN (A1)); 2); ””)}, I would like to know if there is a possibility to use the “SE” command with the “OR” command in (SE (\$ B \$ 4: \$ B \$ 12 = \$ G \$ 4; LIN (\$ B \$ 4: \$ B \$ 12), that is, if there is a possibility to choose more than one criterion for \$ G \$ 4. If so, what would be the correct syntax for the command?

154. Yes, both with the error message and the formula started with the index, are not copying.

155. Hi Camila, sorry for the delay in answering,

a first doubt looking at the inserted formula is if you remembered to press CTRL + SHIFT + ENTER to turn it into matrix.

156. Hi Alexandre, I don't know if I understood 100% well, but if you have more than one criterion, you could use SE within SE. In this post we talked a little more about how to use it - https://blog.luz.vc/excel/funcoes-condicionais-no-excel/

In a quick example it would be something like = SE (A1> 100; ”Excellent”; SE (A1> 150; ”Good”; SE (A1> 200; ”Regular”; ”Bad”)

157. Good afternoon. I would like to use the “SE” function above (PROCV formula) with more than one selection criterion. Ex .: In a column I have several names and I would like to bring not just one, but two or three different names. It's possible ? Thanks.

158. I can not copy to the banknotes underneath, when (A1) is the same on all banknotes, it only copies the table on the left. But varying (A1) this error appears.
How can I fix this? I need only the expenses that are due on the current day to appear ...

159. Your post is very good

It all worked out (Cells in blue)

however I need the information to appear online rather than column

* where is interrogation in red

160. Congratulations on the post.
It all worked out (Cells in blue)
however I need the information to appear online rather than column
* where is interrogation in red

161. Hi Matheus,

unfortunately I no longer have this spreadsheet, but the error is probably in the -3 of the formula. It serves to show the number of rows in the 1 row to the beginning of your table. In case the example were 3, maybe yours is different. Can you test that and tell me if it worked?

162. The formula of the second worksheet is not working for me, I need to do something extremely similar in my work, could you send me the worksheet so I could try to make it from it? Thank you very much in advance.

163. Hi Thiago,

-3 is the difference from the line where you are applying the formula to the 1 row. In our example, the formula takes the reference in the 4 line, so we use the -3. If the value were on the 10 line, you would use the -9

164. Good afternoon I can not understand the -3, could you explain, if I have a bigger worksheet how will I know how many I will subtract?