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

189
55888
PROCV returning all values ​​- end result
Excel Spreadsheets

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).

Excel Course - Conditional Formatting

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

PROCV with 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

PROCV returning all values

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.

Excel Course - Conditional Formatting

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

PROCV with more than one condition - wanted value

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 turns these 2 values ​​into a single value. As we know, the CONCATENAR function is used to group strings. If you want to remember more, check out our post with 5 applications from CONCATENAR. When using it in G4 and G5 cells, we would have something like:

PROCV with more than one condition - value searched with concatenate

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.

Excel Course - Conditional Formatting

  • 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:

PROCV with more than one condition - table modified

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:

PROCV with more than one condition - PROCV with CONCATENAR

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:

PROCV with more than one condition - PROCV with CONCATENAR 2

2. PROCV 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:

PROCV returning all values ​​- 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.

Excel Course - Conditional Formatting

  • 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.

PROCV returning all values ​​- error in common procv

  • 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.

PROCV returning all values ​​- example

  • 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:

PROCV returning all values ​​- end 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.

Excel Course - Conditional Formatting

  • 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))

PROCV returning all values ​​- 1 functions

Note that the only part of the formula with relative reference is LIN (A1). This occurs purposely for when we drag the formula down, it returns the second, third and fourth minor respectively. That's why we got the sales order right. If we marked another salesperson, we would have only the sales made by him numbered in the correct order:

PROCV returning all values ​​- 2 functions

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 ...

  • 3 Step - TABLE OF CONTENTS

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:

PROCV returning all values ​​- 3 functions

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.

Excel Course - Conditional Formatting

  • 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.

PROCV returning all values ​​- 4 functions

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 next column, just copy and paste. Obviously we have to make a change: change the 2 value of the INDEX function to the 3 value. We will do this, because now we want to get the base values ​​(column D) from our auxiliary table.

PROCV returning all values ​​- 5 functions

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.

Excel Course - Conditional FormattingAre you in doubt? Get in on LUZ Excel Forum and send your question!

Excel Spreadsheets
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.

189 COMMENTS

  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 was able to fix it already ... I was putting the brackets and pressing the ctrl shift enter, then they told me that I did not have to put the brackets that he would appear alone, anyway, now it's perfect! Thank you !!! =)

  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: {= SEERRO (CONTRIBUTORS! $ A $ 2: $ B $ 2000; MINOR (IF (COLLABORATORS! $ A $ 2: $ A $ 2000 = $ AND $ 7; LIN (COLLABORATORS! $ A $ 2: $ A $ 2000) -1); LIN (A1)); 2); "")}

    * When I throw the {} the "works" formula, but it returns the 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 using the full function
    2 - instead of = CN8, put the same value in another cell and use this new reference
    3 - the function needs to 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 can not be used this way "SE ($ 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 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 line 1 I have several materials that have been sold (I will use fruits) they are fixed and do not repeat ... (apple [C1], banana [D1], pineapple [E1] will be 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 above formula, however I did not get success, maybe I need to use COL instead of LIN ... I do not know. Would you help me. For understanding I can 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, could be B1, C1, etc - then the possible variation in this case quoted is for another column on the same line

  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 worksheet, but right in the first part where we put the formula "SE with LIN", my excel returns the value "FALSE", even though the name of the vendor I am looking for is within the range established for verification of the SE condition. Can you give me some help, please? Thank you!

  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.
    The first is "classification" (if 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 do not know very well how to help, since the detailing of the formula is already here - try applying by making the change to your table and spreadsheet have questions during the use, send me here that I try to help

  33. Good afternoon!

    I need to pull the information of a tab "Sales" where name of products, unit value and qtde is sold to another tab "Results" where I bring the information only of the products I sold and quantity, because not always seeing the same products and the same qtdes, day to day varies a lot. I thought of basing on the column qdte if 0 is not bring the product to "Result" tab but I do not 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 very much !!! Your examples gave me a light to solve my problem! <3
    I had already tried BDMIN / BDMAX, I was already thinking about 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 have not been able to return the values ​​of the lines, it always returns the value "1", and does not recognize the other names of Jonas and Jarbas, in these cases it returns the "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 for the work, I wanted to ask a question, I have a table with different amounts of data being that I often have the same indívudo in column A in the 1 line and in column B only in the 3 line, I wanted to know how to do " search "the values ​​of the same individual in all columns.
    Since there is in the column table the species (which are the individuals) and the values ​​of each individual in the following table ... and so on, but since there is different amount of individual the same individual changes from "position" leaving the 1 line and going to the 2 line 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 beyond the above formula (which compares only in one column), check a second column, which would be work orders. For example: in the OS_004 = 9 single boxes; in OS_007 = 8 single boxes. The idea is to have only one worksheet with listing all the boxes of all "OS" and make a summary table with the formula to know how many boxes there are of each OS.

  45. It's the best option in my opinion, but it leaves a lot to be desired in large database where the formula leaves everything slower, too much information to process without mentioning that we want more of a return result "(in my case of course).

    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 this case, it would look something like = SOMASE (JANUARY! $ A $ 1: $ A $ 100; "Subsidiary 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 search in another worksheet the Second lowest item with condition, so far I have not found any formula or combination that gives me this result. With MINIMOSES () I can return the smallest, but how do I return the second, third ... .. smaller with condition ???

  58. That's right I used two variables but even so I still have the same value ... I'll follow your suggestion and create a variable that makes the distinction to facilitate. Thanks

  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. I have a problem and it involves having equal values ​​... I am using the following formula: = $ INDEX (Returns! $ 2: D3171; CORRESP $ 2 & Returns! $ 2: E2; 3171))
    I used this instead of Procv because I need it to parse two variables before I get a die. The problem is that there are rows in which 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 another tab, in one there are two equal cases and the other only one, I would like it to draw only result in the first and second as it already showed that it gave # ND ... Can anyone help me ???

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

    abs,

  62. Yeah ... I wanted to show you the little table, just so you can understand the formatting. I wanted to use simple filtering 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 PROCV is giving # N / D error but when I search for the item through "Find" (CRTL + L), it finds the item. The strange thing is that when I look for the item in another tab (it's a list that I repeat in several tabs), it finds! I think it's some cell formatting but I do not know what. Can you help me? Thank you!

  66. Hello Rafael, I have a table (I think basic) to do, but I can not draw it. The table below describes an example of what I need. Each company is unique (it does not repeat itself); countries are diverse and repeat themselves. But when I will specify the product of 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 (for then I go in the country, for example and I know what products are produced, or if I want to go directly to the Product and see which country has production!). However, when I generated this kind of worksheet, I did not want to pollute it and write several times = THINGS. - Brazil - Dolls - Plastic - Small; THINGS - Brazil Dolls - Plastic - Large ... etc).
    But I can not filter without writing the corresponding information on each line. If I do not write everything, the Great Plastic Dolls of COASAS LTDA. do not appear in the filter !!! How can I solve 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 have the feeling of being something easy !!!

    THINGS LTD. - Brazil - Dolls - Plastic - Small
    Grandes
    Cloth - Small
    - Big ones
    BRINQUEDOS SA - United States - Carts - Plastic - Small
    - Big ones
    - Wood - Small
    - Big ones

    How could I solve this 'array', since each row has columns that correspond and I can not merge (for the table to be 'pretty').
    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!

    Got it. I used in parallel 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 in the same example in "same fields and values" it does not return the smallest value but the first value for its criterion.

  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 marked "green", I made it a point to mark the same "green" columns in my control, but the Custodian sends the data of the Funds in the same worksheet, repeating the names of each Fund column "D") and in our spreadsheet, the Funds described are available in "separate" tabs for each Fund, so in my worksheet there is a guide to the "Seagull" Fund, another "Blackbird" fund guide, the "Woodpecker" fund, etc ... I need to extract the data from the source worksheet (where all the data of all the Funds are available in the same worksheet) and post them within each respective tab of each Fund. Got it? So I can not just use a "PROCV" because 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 / the file name of the custodian ...) + INDEX (in an attempt to create a declared array) ... But it did not work, because INDIRECT is not working ...

    Anyway, since I need to copy data from multiple rows (and the number of rows can change daily, due to varying numbers of operations within each Fund per day, I still can not find a clever way to get this data in the face of the daily changes in rows qtd (I have tried to standardize the sending of the data in the same way that we have completed our controls here, but there was a negative due to the fact that the data generated by the Custodian is performed by a "BI" and could not change the model (customizing for our need ...) ... then the data continues to be treated MANUALLY, being extracted from a spreadsheet and transferred to ours, archaically and not very productive.

    I'm going to need to create a macro that transforms the updated data (D-1), at the end of each of these guides, to avoid the file being "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 our custodian company. I need to extract data from this column to another worksheet, but the data is repeated (in this case I can consider a KEY!) And I need to extract ONLY the data from the 3 columns next to this key ...

    Initially I tried to set up a "PROCV" with DESLOC ... it did not work ...
    Then I tried to use a formula with INDEX and LIN ... tmb did not work ...

    That is: I need to extract from the first worksheet and insert in the second worksheet, with search parameter for repeated data (in this case, names of Funds) ...

    https://uploads.disquscdn.com/images/966ae6f21015c65b8b52289e4c35c975fc43a968d983b411b308c4bb179d162e.gif

    https://uploads.disquscdn.com/images/d820005cbceab39424653d07ade7ba56eeb3278618ab55f6586e0200b9e3242e.gif

  85. If a formula already exists in the cell that displays the value zero, you could use an additional SE 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
    thanks in advance

  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 flaps, one release the paint maintenance data on the highway and the other shows a Scrapbook per km with the situation of it. I need the Retigráfico worksheet to show me the value in the field of "Reflectance" according to the last value released and coincident with "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 ... Is there any limitation on the amount of data? I'm using the formula to bring a search into a worksheet (plus 1000 rows and 50 columns) with many repeated CPF's. But when trying to return the results from the "I" column gives 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. Encounters all result columns from "B" to "H" but when trying to bring the results from column "I" onwards 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 ('; C10;' ;!) function to search for the value that is in C10. If this does not 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 RI plan is where the data is and the 2 line is where the titles of each column are, and the 1 line left to number each column to make it easier to search the data for each column ... they 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: = PROCV (C7; '10'! A9: H116; 8; 0) I need that where 10 is the name of a worksheet 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)?

    Thanks in advance.

  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 can not 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 row function will analyze the range we want to use and the fact that the -3 has is because the table starts on the fourth row. If your table starts on the first line, you do not need to do this

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

  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? = SE (procv = N / D; "active"; PROCV (C4; Plan4! $ I $ 6: $ J $ 23; 2; FALSE)), since 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;

    that I looked for the value of the A1 cell in the "plan1" spreadsheet in the range a1: b20, I would return the contents of the 2 column, but if it did not envy the value of the A1 cell in the given range, I would return "Active".

    to find the value I made procv, but I could not put a condition if 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 mark and section and some PROC function (PROCH and PROC). But it did not work. I do not know if I did not know how to use it. Follow the function used: = SE (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 get a formula to find the values, for example: I have a column with X value and it repeats 3 times, I need to fetch those 3 values, without sum ... I get using 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 you the result I am looking for, HOWEVER I need to CLASSIFY my SELLERS in GROWING order and they will alternate according to the change of 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 was able to solve by creating an extra column of SELLERS and establishing this as a "criterion" for SOMASE, leaving it out of my classification, however I want to solve 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: ansolin.mauricio@gmail.com

    Thankful!

  139. And there Rafael, blz?

    I've always been following the post here on the blog and many formulas have already helped me a lot, but this time I ran into an obstacle ...

    in a listing I need to find the 5 greater results, however some names repeat and values ​​tbm ...

    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. It does not quote Matthew because PROCV only finds a value corresponding to the number 13. In this case you would need to create an auxiliary column by adding a minimum value (different per row) 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. rather than I search the data to fill in a column (column named with the reference to be searched, in this case the name "Jonas") need to fill in the data displayed 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, trying to make a spreadsheet in which, I consult a name and it gives me the sales value I'm done in the month, in the case each day and a "tab" spreadsheet, then it would look for the value of each day that was buyer and would give me the final result already added, understood? lol

  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 do not think I was very clear, I apologize. Let's look at the following example: in the following formula {= SEERRO ($ B $ 4: $ D $ 12; LOW (SE $ $ 4: $ B $ 12 = $ G $ 4; LIN $ B $ 4 : $ B $ 12) -3); LIN (A1)); 2); "")}, I would like to know if you can use the "SE" command with the "OR" $ 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. The correct syntax of the command? Thanks in advance for your attention.

  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. Good afternoon. I would like to use the "SE" function above (PROCV formula) with more than one selection criterion. Eg: In a column I have several names and would like to bring not just one but two or three different names. It's possible ? Thank you.

  157. 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 those expenses that expire on the current day to appear ...

  158. 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

  159. 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

  160. 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?

  161. 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.

  162. 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

  163. 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?

Comments are closed.