How to use the Excel VLOOKUP (VLOOKUP) function

119
66579
Practical example of using the PROCV formula of excel in employee database
Excel Spreadsheets

Whenever I see managers using Excel spreadsheets, it becomes clear to me the need to use PROCV. For those who do not know what this acronym is or does not have much intimacy with Excel, it is one of the formulas to search for information that Excel provides for its users and is very little used for looking "complicated".

When finished reading this post you will learn how to use the VLOOKUP function in Excel for any business situation.

Excel Course - Conditional Formatting

If you're a little lost, rest easy, I'll show you some examples of using PROCV, present the function in full with a very detailed step-by-step and at the end of this post, I'll let you download an exercise worksheet with feedback.

Let's start by looking at some examples:

1. Example of use of PROCV in Employee Control Worksheet

Imagine you have a spreadsheet with a record of all your employees' information. Basically, it would have a similar view to our Registration and Employee Control Worksheet below:

Employee Database

Realize that all employees have a number of information registered such as your company's registration number, gender, date of birth, age, birthday month, etc. Now imagine that you have a list with 100 employees and every time you want to get the data from one of them will have a monstrous job both to find the data and to use it.

Excel Course - Conditional Formatting

It is for these and others that Excel created the PROCV function (or VLOOKUP in English). With it, you can do automatic searches for information in your spreadsheet. In this case, we want to do searches for the name of the employees. Let's see how it works:

Capture 2

In our spreadsheet we created a new tab (3 Employee Query) where you can choose the employee name from a list in the D7 cell as indicated in the image above. From this choice, with the use of the PROCV formula, Excel automatically searches the employees' registration tab and takes all the corresponding information to the cells with the search formulas. In our case, we chose the employee Amanda Santos. Let's see how the result was:

Practical example of using the PROCV formula of excel in employee database

So in this first example, to get the date of birth, the age and the CPF of our employee, we use the construction of the formulas in this way:

  • For the date of birth:
    = PROCV ($ D $ 72. Employee registration '! $ AJ $ 7: $ BM $ 500;4; 0)
  • For the age:
    = PROCV ($ D $ 72. Employee registration '! $ AJ $ 7: $ BM $ 500;5; 0)
  • For the CPF:
    = PROCV ($ D $ 72. Employee registration '! $ AJ $ 7: $ BM $ 500;8; 0)

Note that the 3 part of each formula (blue items) has different numberings for each item searched. This is because in our employee record tab, each of these information was placed in a different column, and this part of the formula matches the positions of such columns.

Excel Course - Conditional Formatting

You should also have noticed that the name of our selected employee (Amanda Santos) has been reported in cell D7 (in red), so when the employees' names are changed in that cell, the formula is recalculated automatically based on the new name added.

As you can see, the VLOOKUP function is very simple to use and very functional. You should use it whenever you want to do vertical searches (in columns). It has a variation, the PROCH function, which searches for the horizontal (in lines). In this post we will focus only on the VLOOKUP function. To decide which of the two functions to use, observe in your spreadsheet how the values ​​that may be the result of the search are distributed: if the values ​​are in a column use the function PROCV, and if the values ​​are on a line use the function PROCH.

Let's show another example of using PROCV.

2. Example of use of PROCV in Inventory Control worksheet

Now let's look at the example of a inventory control sheet. Here you will find all the merchandise information you have stored in your company. This is the view you would have of your products in stock:

product registration in stock

See that all products, as well as in the Employee Master worksheet, have a series of data and information related to them, such as minimum stock, current stock, status, etc. If you want to see information about a certain product, just the VLOOKUP function (or VLOOKUP in English). In this case, we want to search for the name of the goods / products registered. Let's see how it works:

How to Use the Excel 1 PROCV (VLOOKUP) Function

In our Inventory Control Sheet we created a new tab where you can choose the name of the product in a list in cell C7 as indicated in the image above.

Excel Course - Conditional Formatting

From this choice of product, after using the PROCV formula, excel will search the Inventory tab, taking the information corresponding to the product to the cells that we created. In this case, we choose the pointing product. Let's see the result:

Practical example of using the PROCV formula of excel in the database of product stocks

In this second example, to get the minimum inventory and stock quantity of the pointing product, we made the formulas in this way:

  • For minimum stock:
    = PROCV (C72. Inventory '! $ C $ 8: $ D $ 508;2;FALSE)
  • For quantity in stock:
    = PROCV (C72. Inventory '! $ C $ 8: $ E $ 508;3;FALSE)

As in our first example, the 3rd part of the VLOOKUP formula (items in blue) has different numbers, since in the Inventory tab, this information was placed in different columns. In addition, the 1st part of the VLOOKUP formula (items in red) is always the same, since it is the cell related to the value (name, product, etc.) that will be searched for.

Excel Course - Conditional Formatting

Other Applications

You could still use Excel's PROCV formula to do searches and searches on almost any database you have. For example, you can get more information from sales order records, customer tables, for cash control, etc. Below you will see an example of various financial information that is automatically PROCV formula in our marketing plan worksheet:

Practical example of using the Excel PROCV formula in a marketing plan worksheet

According to the media chosen, the values ​​of available budget, cost, revenue and result are automatically changed according to the values ​​already added in other tabs of the Marketing Plan worksheet.

Excel Course - Conditional Formatting

3. Understanding the PROCV function conceptually in Excel:

What is written when you enter the VLOOKUP function (vertical search) is PROCV (search_value, table_array, index_num_num, range)

PROCV formula syntax

Where:

search_value: it is the criterion used in the search, that is, it is the value to be located in the first column of the matrix of the table. In our examples, the reference items were employees (example 1) and products (example 2).

matrix_table: is the entire range of data, comprising rows and columns. It will always consist of two or more columns of data. The values ​​in the first column of table_array are those used for the lookup_value. These values ​​can be text, numbers, or logical values. In our examples, the table array was the databases where we put employee information (example 1) and products (example 2)

index_num_column: is the column that contains the value to be returned. The first column has a value of 1, the second 2, the third 3, and so on. In our example of the Employees worksheet, when we wanted to use the VLOOKUP formula to find the date of birth that was in the fourth column of the table matrix we used, we put the value 4 in num_index_column (which in our example was in blue). If this value is:

  • Less than 1, PROCV returns the #VALUE! Error value.
  • Greater than the number of columns in table_array, PROCV will supply the #REF! Error value.

Excel Course - Conditional Formatting

interval: defines the accuracy of the search. It is a logical value that specifies whether you want the VLOOKUP formula to find an exact or approximate match. If it is TRUE or 1 an approximate match will be returned. If an exact match is not found, the closest major value that is less than the searched_value is returned. If it is FALSE or 0 (zero), PROCV will only find an exact match. In this case, the values ​​in the first column of table_array do not need to be sorted. If there are two or more values ​​in the first column of table_array that match the lookup_value, the first found value will be used. If an exact match is not found, the # N / D error value is returned.

Note: The value to be searched should always be in the first column, otherwise the VLOOK function will not work.

Excel Course - Conditional Formatting

4. Now let's see the step-by-step practice in how to use PROCV

To do the step by step in the practice of setting up your VLOOKUP I will use the LUZ Employee Registration and Control Worksheet, but think of it only as the general structure to apply in your excel spreadsheet.

1. Structure

You will need to have a table structure, with at least 2 columns. The first column will always be your reference and from the second you will have the values ​​that can be searched for. To be less abstract, let's look at the table in our worksheet. To get easier, we simplify to a table with 5 rows (non-account header) and 6 columns (name, date of birth, email, CPF, RG Dispatcher):

employee data table

Any table that you have with information in rows and columns can be used for the PROCV function. Just remember to have your reference value as the first column. In this case we are presenting here, the first column is that of the employee's name.

2. Inserting the formula

The PROCV function searches for the occurrence of a text in a particular column and returns the value of another column (which you will specify in the formula) of that same row. In our case, we will create a form structure in another tab to insert the formula:

Table for use of PROCV

Note that in our structure, the reference value will always be the employee's name (first column of our database). Now that we have the structure, we can choose the employee (in this case we will use Carlos Carvalho as an example) and then insert the formula. For this, you will choose the cell corresponding to the value you want. Let's start with the birth date value (cell D8).

using procv to add date of birth

In this cell we will put the formula = VLOOKUP ($ D $ 7; '2. Employee registration'! $ B $ 3: $ G $ 7; 2; 0)

procv formula by length

In this formula, we can see that:

- search_value = $ D $ 7 - which is exactly the name of Carlos Carvalho

- matrix_table = '2. Employees' tab $ B $ 3: $ G $ 7 - which represents the table that we show in the image above. The tab name appears in this formula because we are using it in another tab. If we were doing the formula in the same tab would just put B3: G7 here

- index_num_column - We put the number 2, because the date of birth is the second column in our database

- [search_interval] - We put 0 (zero), but could also be the value "false"

Excel Course - Conditional Formatting

If you prefer, use the formula wizard. To do this, Click the tab Formulas> Insert function> PROCV. There, you will have these same 4 fields to fill:

Excel formulas help window - PROCV

When filling in the formula correctly and pressing the enter key, you will see that the birth date will automatically appear in the correct value:

procv formula in practice

3. The best way to use

If you continue to apply the VLOOKUP formula to the other cells (email, CPF, RG and Exp. Organ) we will have the table completely filled with all information. Don't be content with just one result, you can get complete information for a given information in a single tab or table as shown below:

complete procv table

Additional Comments

  1. Pay attention to the values ​​in the first column of table_array. Always check for possible left spacing errors, inconsistent use of quotation marks, date, or numbers as text or nonprinting characters. In these cases, the PROCV function may provide an incorrect or unexpected value.
  2. To make life easier for anyone using any excel formula, you can use the $ character to lock the formula and then use the drag (or copy and paste) cells function to not have the rework create the PROCV formula multiple times. I'll tell you more about how to do this in future posts

Give your opinion and suggestions

Tell me what you most want to know about excel. Write below in the comments what you think of this post. Like the examples? Did you get the PROCV formula on your spreadsheet? What other formulas and functions do you want to learn more about?

Test your knowledge

Download one now worksheet on the PROCV function with feedback.

Excel Course - Conditional Formatting

We have some ready-made spreadsheets that use the PROCV formula:

1) Performance Evaluation by Competence

2) Employee Registration and Control

3) Marketing plan

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

Excel Spreadsheets

119 COMMENTS

  1. Hi Luciele, you can create a formula that is the previous line + 1, for example, in A2, you would have = A1 + 1. Then just drag the formula to the rest of the cells that will have the sequencing

  2. Good afternoon Rafael

    I would like some help, I have a Google form and I created the spreadsheet from it, but I would like each new line to create a sequential identifier code, starting with 1, 2, 3… how do I do it?

  3. Hi Laerte, you can use VLOOKUP with SE - for example = SE (VLOOKUP () = 1; 1; 0) - obviously the VLOOKUP formula needs to be written

  4. I would like to know which formula to use in a search where the searched value is found, returns with one result, however, not finding, returns another. Type this: I ask you to look up the 15 number in a column. If it finds the number 15 returns with the value 01 in a particular row / column, but if it does not find it, it should return the 00, that is, if positive 01 and if negative 00. Has as???

  5. Good morning Rafael !, Thank you!
    It was simple, and I did not see, really only sees the problem who thinks outside the problem! lol
    This blog is a show, keep it up!
    Congratulations and success.

  6. Hi Andre, in the RANDOM function itself, you can put the references A1 and B1 instead of setting fixed values

  7. I forgot to say that I already have a button that generates random between fixed values, however I need this button to perform a random of the values ​​typed in these examples above.

  8. Good Afternoon!
    It would have some macro to pick up the values ​​of two Ex cells: (A1, B1) and generate a random of these two values ​​in cell (D1).
    Note: I will need to always edit the values ​​(A1 and B1)

  9. Hi Ivan, PROCV is used to fetch cell values, an image is a bit more complicated and may only be possible with VBA code, dynamically searching for the element used

  10. Hello Rafael, how are you?
    I work with PCP and use PROCV a lot… I am creating a “CONSULTATION TERMINAL” tab, where in this tab I type the name of the client and she brings me pertinent information. But I'm trying to bring the image, but I'm not getting it. In the registration tab, I placed each customer's logo in front of their name, and in a single cell. But when I type the customer code, Excel is not bringing the image. Could you help me?
    Thank you.

  11. Hi Héric, to standardize this my main suggestion is to use the validation of list data in the typing field (F16) of full names. So, instead of typing, the user will have to choose one of the names from the list, avoid typing errors

  12. I have a spreadsheet very similar to the one you use as an example! I managed to solve the problem of having two people with the same name, performing the search as the full name. However, when typing the employee's name, errors can occur, either typing (example Sérgio Xavier and when searching for the operator, typing Sérgio Chavier), forgetting an accent (Sérgio Xavier and when searching, the operator types Sergio Xavier), which causes an error in the search and has caused me great inconvenience…
    How can I do to solve, considering that the only factor that differentiates employees is in fact the full name ...
    The formula I am using is = EVERYTHING (PROCV (F16; Z301: AP605; 2; 0); ”NON-EXISTING”) and in a field above I test the response of this cell = IF (D25 = ”NON-EXISTING”; ”INCORRECT NAME OR NOT IMPLANTED ”;” ”)

    Please, if possible, give me a light.

  13. Hi Luiz, maybe if you create a tab for each employee and have your entire workbook print out easier, but it will be hard to do

  14. Okay, now I want to print in sequence using the PROCV command on only one worksheet, though with multiple employee records. Until then I have to change the Searched Value sequence every time, which makes the printing process very slow.

  15. Hi Rafael, because you do not use CONCATENAR to join the code and the location and in the database does the same thing with all the values ​​adding a column with the concatenation of these two

  16. Good afternoon, Rafael, how are you?

    I have a question, I would like to find a value in another worksheet, from VLOOKUP it is possible. However you would like this value to be located two items are recognized. Ex:

    Column A - Code
    Column B - Location
    Column C - VALUE TO BE RECOGNIZED (being that it must respect code and location)

    How can I do a PROCV / VLOOKUP this way? I do not think the explanation is so ugly.
    Thank you very much

  17. Yes, it is possible to use PROCV with the TRUE criterion at the end of the function notation

  18. Good afternoon.
    I need to find the nearest or largest value.
    Example:
    Value to be found 40.
    I have the following 30, 35, 42, 46, and 50 values.
    I need that in the search, if the formula does not find the 40, bring the 42. It's possible ?
    Thank you

  19. Hi Agustin, this is enough to have a column with commission values ​​(which must have a conditional SE to dynamically calculate the amount referring to the commission) and then add these values ​​with a SUM function

  20. Oops, I have a spreadsheet regarding my goals / commissions per month, I have all the information about the sale and this value is defined by the payment method and the chosen plan and if it is an upgrade or downgrade. The sales quantity tb determines the commission amount eg: 85-94 R $ 1,00 // 95-104 R $ 3,00 wanted the spreadsheet to give me the commission information information automatically based on the information.

  21. Hi Isabel, it all depends on how your spreadsheet is organized and whether that data is automatic or not.

    If they are not and you are doing the list in some cell, for example the A1 cell, you just have to create a conditional IF formula in another cell as well

    = SE (A1> 70; ”You can rest”; ”You still need to hit the goal”)

    So she'll let you know if you've hit the target or not. Obviously you can change the texts to whatever you want

  22. Hi, I have a problem, see if you can help me, I work with reversions and goals to achieve, for example, I have a daily goal of 70,1 and several calls a day, say 10 calls entered and 10 4, I reverted and 6 I canceled, ie I did not reach my goal, I need an Excel formula 2010
    that tells me how many more I need to revert to hit my goal, being that I can stay in line to receive more calls.

  23. Ok, Rafael once again thank you, I will try using the procedures quoted,

  24. Hi VIlmon, to change the line you need to use the PROCH.

    If you need to change row and column, you can use the OFFSET function to do this

  25. If I change the 2 number changes only the column I need to change the line. Obg Rafael

  26. It depends on the organization of your Vilmon worksheet.

    From what I've seen here your formula is wrong. The 2 value that you hold fixed needs to be changed so that the formula searches for new values. And the field you have switched from 0 to 1, in your case, must always be zero. Like this:

    Line Person 1 = VLOOKUP (B8; 'Database'! A11: I700; 2; 0) [Maria gomes]
    Line Person 2 = VLOOKUP (B8; 'Database'! A11: I700; 3; 0) [Paloma gomes]
    Line Person 3 = VLOOKUP (B8; 'Database'! A11: I700; 4; 0)
    Line Person 4 = VLOOKUP (B8; 'Database'! A11: I700; 5; 0)
    Line Person 5 = VLOOKUP (B8; 'Database'! A11: I700; 6; 0)

    This will only work if the other members are soon after the person 1 and 2, just change the numbering of the

  27. Hello Rafael can you help me with a formula.?
    I'm in need of a little help, I'm an argente of health and I'm making a spreadsheet where I can facilitate my work. I already have a complete database with number of records, name etc., but in another tab I am building a control panel where I can collect the information of each family by the number of records that I type corresponding to what is in the database . where should I return the names of each person.
    What I'm getting is just calling 2 members via the following formulas

    EX.:

    -> In the database tab I made a field to enter the medical record number:
    Number of records [2]
    -> A field that tells me whether or not it exists through this formula:
    = IF (CONT.SE ('Database'! A11: A600; 'Control Panel'! B8) = 0; ”Doesn't exist”; ”Does exist”)
    [ EXIST ]
    -
    > And the fields to return people's data separately

    Line Person 1 = VLOOKUP (B8; 'Database'! A11: I700; 2; 0) [Maria gomes]
    Line Person 2 = VLOOKUP (B8; 'Database'! A11: I700; 2; 1) [Paloma gomes]
    Person Line 3 Other members
    Person Line 4 Other members
    Person Line 5 Other members

    But I can not call the other members of the family group
    I already tried several functions and I do not know how else to do?
    Am I on the right track or should I use other rules?
    Can you give me a light?

  28. Very useful and practical this article! Thank you so much, it was everything you were looking for!

  29. Hi Israel, unfortunately I do not know how to solve this problem you reported. With duplicate values, the PROCV answers the first found, which generates a failure in the response. I thought about using CONCATENAR to have a unique value, but if you have 2 different values, one for latitude and another for longitude will no longer be able to use this formula. Maybe using DESLOC and CORRESP is possible, but I imagine you'll still run into the problem of duplicate and approximate values

  30. Guys, good night!
    I need to search for a PAR of numeric values ​​in a database as follows:
    I have a database that among several other data has a column with latitude coordinates and another column with coordinates of longitude, in two different cells are entered random coordinates (latitude and longitude), I need a return in a third cell of the database spreadsheet that corresponds to the value of the coordinate type, detail is that these values ​​are not necessarily equal being approximations, another detail is that in the database there are repeated values ​​of longitude and also repeated values ​​of latitude, would like help to try to solve this problem. Thank you very much in advance.
    Att. Israel

  31. I do not quite understand how the values ​​are organized, but at first you will be able to add the values ​​conditioned to a board using SOMASE

  32. Good Day
    I'm having trouble finding a formula for my spreadsheet, I have a spreadsheet where I inform the daily trips of the company's vehicles as well as the costs with fuel, tuition, driver's and helpers' diaries, and I set up another spreadsheet where I have all the vehicle plates and I need to make the total sum of the costs of each vehicle, for that I need to create a formula where Excel looks in the vehicle column (the vehicle corresponding to the plate I need and in the column of the cost values ​​automatically adds the values ​​corresponding to the plate I need ... .ex.:
    1ª Worksheet:
    PLATE Revenue Expense
    Freight Fuel Daily Toll Loading / Unloading
    FKI8142 R $ - R $ - R $ - R $ - R $ -
    EQU3699 R $ - R $ - R $ - R $ - R $ -

    2ª Worksheet
    Date Vehicle Car Expenses
    R $ Fuel Daily toll Vlr Download
    17 / Jan FKI-8142
    17 / jan EQU-3699
    17 / Jan FKI-8142
    17 / jan EQU-3699

    I need excel to look in the vehicle column for the FKI-8142 card and add all the values ​​in the Fuel column corresponding to the card I want and bring this sum to the 1plot.

    can you help me?

  33. You can use a conditional count function (CONT.SE) and make the value of the 1 worksheet equal to the value of the 2 worksheet. If the result is zero, you can use red conditional formatting to tell you better

  34. Hello friend, I have two spreadsheets inside the same workbook. The folder is from an inventory of computers, in the 1 worksheet I have data of the location of the equipment with its name, in the 2 worksheet has the serial, model and etc. In both has the name of the equipment, which should be the same. I would like you to be prevented from registering a name in the 1 worksheet, if it does not appear on the 2 worksheet.
    This is to prevent them being updated distinctly. how I do?

  35. Dude you can make things much clearer, thank you very much for this blog, it is helping me a lot.

    Thank you.

  36. In these cases I am always in favor of creating a single value using the CONCATENATE function and fetching this unique value, so I do not fall into the problem of repeated values

  37. OPA I HAVE A SIMILAR TABLE, IN A COLUMN I HAVE TO ENTER THE CODE OF THE PRODUCT AND I NEED TO IN ANOTHER COLUMN APPEARS THE AUTOMATIC DESCRIPTION CORRESPONDING TO THE CODE DIGITED.
    MY NAME IS MARCOS

  38. Hello Rafael, I need your help. I have a spreadsheet that has DDD, Locales and their regional.
    It turns out that there are some locales with the same name, but different DDDs and regional.
    How do I proceed to create a correct procv so that I pull the correct data and not first given that come in the search?
    Being that when entering the DDD and the locality, he has to pull me to the correct regional.

  39. Hi Luciana, how are you?

    A formula will always change when you change the data source it seeks. If you want to put it one at a time, you would need to create a macro that copies and pastes the items as values, so when you change the font, the result would remain the same

  40. Hi Rafael.
    I have a budget worksheet to do and I would like it when you select the product to automatically update the value to then multiply and create the budget.
    Without creating PROCV every time you select another product. Do you understand what I mean?
    I've seen a spreadsheet like this but I do not know how it works.
    Thank you.

  41. Hi Olivio, I'd rather send the answer right here. Apparently it will be a long, but simple answer. Just use the nested SE function inside other conditionals. It would look something like this (instead of X I will use A1, because it is the reference of the cell where the value in cubic meters will be).

    = SE (A1 <10; 34,4; SE (A1 <20; 34,4+ (A1-10) * 6,98; SE (A1 <50… and so on, in the end just close all parentheses OK?

  42. Hi Rafael!

    I need to set up a spreadsheet for calculating the water bill where entering the consumption in m3 in a given cell, the calculation of the value of the account appears in another cell. However there are predetermined consumption ranges for this calculation with different values ​​for the cost per m3.

    X being the consumption in m3, the bands for this calculation follows below.

    0 <X <10 - pay a fixed amount of R $ 34,4

    11 <X <20 - pay R $ 34,4 + (X-10) * 6,98

    21 <X <50 - pay R $ 34,4 + 10 * 6,98 + (X-20) * 10,48

    51 <X 100 - pays R $ 34,4 + 10 * 6,98 + 20 * 10,48 + 50 * 15,12 + (X-100) * 17,42
    If possible you can forward the response by email = [email protected]

  43. Hi Mark, how are you?

    It depends exactly on how you want to do it. A very simple way is to make simple references between worksheets, using the = sign and placing the exact reference, then dragging to the rest of the worksheet.

  44. Hello, I have a question.

    I have two spreadsheets that are populated by different people, but the fields / fill columns are default. I would like to create a third worksheet (database type) that brings all the filled rows of the two worksheets, you know how I do?

  45. Rafael, how are you?

    I have a VLOOKUP that is bringing wrong values, due to the format of the search cell being GENERAL and not NUMBER. But there are 10.000 items, as I do to update automatically. For example, when I format the cell for number, and press F2 ENTER, the VLOOKUP works, because it finds the correct value, but I can't do it in 10.000 lines …… how can I automate?

  46. Can I use procv to elaborate an index by theme? example I want to search in my spreadsheet all the texts that I classify as LOVE…. I want that when looking for the theme, look for what I'm looking for, only the filter, autofilter, returns, but it doesn't look very good aesthetically, what do you think?

  47. Hi Bruno, in this case I create an auxiliary column with small values, but that will always make the values ​​are unique and a column adding the original values ​​with the ones I added. For example, in a list of 3 values ​​would look like this:

    value 1 - 100 + 0,00001 = 100,00001
    value 2 - 200 + 0,00002 = 200,00002
    value 3 - 100 + 0,00003 = 100,00003

    Thus, the ordering of the final result in the side column is not impaired by results that would be the same

  48. good afternoon!
    I have a sales release spreadsheet, where it has the rank of 1 to 15 placed. I use procv to return the name of the products that are in each collocation but when the value is equal it returns me the same product being that they are different but only the sales value that are the same. I'd like to know how I can solve this problem.
    thank you

  49. Hi José, have you tried using PROCV? I believe that if your registration is correctly done on the shopping cart tab you will have the result you are looking for

  50. I would like to know if there is any function that returns the following to me: I have a database in EXCEL (Spreadsheet) where I record all the monthly purchases I make on the market. So I need to know the last amount paid and the date of a certain product, without using the BD functions…. It's possible?

  51. Hello .. Good evening. I have a vehicle handling worksheet, where I would contribute from my company places data of the exits and entrances of the vehicles, data such as kilometrage of arrival and departure. it happens that the same vehicle leaves the company several times and I need a function that shows me the most recent mileage of the vehicle.

  52. In this case you can use CONT.SE.

    I assume you have a list of visitor names, right? In it you will only have the name of the visitor once and on the side you can create a column by making a CONT.SE of that table of registers that you attached image and using the reference of the name.

    If you pass 3 you can create a conditional formatting to change color and get your attention

  53. It would be a spreadsheet with name, CPF and date.
    In this worksheet would be placed the data of the people who access.
    However, in order not to be looking for the person who has already crossed 3 accesses, I would like the spreadsheet to alert me when this happens.

  54. Hi Julio,

    explain to me how to count and record the number of people's accesses? Do you write João> 1 access, Carlos> 3 accesses, etc? Or is it otherwise? Explain to me this process of using the spreadsheet that will help me tell you what is possible or not

  55. Good afternoon, Rafael!

    I do not know if you could help me, I need to set up a people access worksheet.
    In this worksheet I would like to add a name and CPF and I wanted it to change color when the person reached a certain number of accesses (in this case they would be 3).
    If you can help me, I'm grateful. If not, could you tell me where to get this help?

    Hug

  56. Rafael, I did what I did but it did not work very well.
    I do not know if there is another way.

    You told me to use it as follows.

    = LEFT (A2; function or reference here of character count number)

    in the image I defined the 1 line as = LEFT (A1; 2) and gave the result I wanted, already in line 2 if it was a client with three initials BRF123l5 I defined as = LEFT (A2; 3) it accepts and gives me the right result, but in practice when I collect the products will be able to enter both code of 2 letters in the beginning qto of 3.

    and that's the problem because on line two if I type aa1234l5 in column C where it is to show the name of the client, it will show me 0 and in column B it counts as aa1.

    I do not know if you can understand it but that's it rsrs vlw abç

  57. In this case you can use a function in the auxiliary table to determine what character value you want in the LEFT function. It would look something like.

    = LEFT (A2; function or reference here of character count number)

  58. Blz Rafael, one more doubt arose.
    It will have cases that the initial will contain more than 2 characters to define the client.

    I realized that = LEFT (A1; 2) increasing the number at the end it goes beyond, but how to determine this for some yes and for others not?
    it's possible?

    mid = gray
    aa = blue
    bb = green

  59. In the case of PROCV, just use the idea exposed in this post. In the example, you would have to create a new table in the style below:

    Column DE
    1 AA Line Blue
    2 FE Yellow Line
    3 BB Green Line

    And in column C, where you have the client, would use the PROCV formula as follows:

    = PROCV (B2; D1: E3; 2; FALSE) so it will look for the value in B2 in the created helper table and will return the value in the second column, which is the client type

  60. Great Rafael,
    Very good!
    Out of curiosity how would you look in PROCV? Does the above tutorial apply?
    I think it would be simpler too if you have to make future upgrades.

    O = SE that posted worked perfectly.

    Vlw
    Thank you!

  61. Of course we can Roy,

    Just create an auxiliary column (as column B has nothing written, you can use it) with the function = LEFT (A2; 2) and drag

    This will show the initial 2 and then in column C you use the SE function within SE depending on the number of client types you have. For example, if they are only 3 as in the image, it would be something like

    = SE (B2 = ”AA”; ”Blue”; SE (B2 = ”FE”; ”Yellow”; ”Green)

    If you want to see more of the IF function, enter this post - https://blog.luz.vc/excel/funcao-se-excel/

    an alternative would be to use PROCV to find which type of client, but you would need to make an auxiliary table with the acronyms and names of the clients

  62. Good morning,
    I need to create a spreadsheet that would make my life much easier.
    Basically it would work as follows:

    Column A would be populated manually with code for each product and column B would show which client it belongs to automatically.

    What would determine the client is the initial two letters of the code.
    eg
    Whenever you have AA it will belong to the blue customer regardless of what is in the middle or the end.

    can you help me?

    Thank you!

  63. Good morning,
    I need to create a spreadsheet that would make my life much easier.
    Basically it would work as follows:

    Column A would be populated manually with code for each product and column B would show which client it belongs to automatically.

    What would determine the client is the initial two letters of the code.
    eg
    Whenever you have AA it will belong to the blue customer regardless of what is in the middle or the end.

    can you help me?

    Thank you!

  64. Hi Mychell, how are you?

    you can use DESLOC to select this range between 01 / 01 and 15 / 01. In order to count how many cells should be selected, I believe a CONT.VALORES should work.

    If you want, it should be possible for you to create a start date cell and a cell with the end date, and then make a SUMMOS looking for criteria greater than the starting date and smaller than the end date.

    Hugs

  65. Friend, I have a rather urgent question.

    I have a table as follows:

    date - product - price

    using the proc v I was able to make another table find the first price for me and last based on the date.

    But I need him to select and add that period (from first to last).
    So that I can leave the table free, after all the last one located is not the last date.

    I imagined a way to do it but for that I would need the procv to return the cell reference, for example. “C3” and not the value found in it “R $ 50,00”, for example. So that I could make a sum based on a period that I type.

    Period - 01/01/15 - 15/01/15 = total amount.

    Thank you very much.

  66. Data validation> List is a feature, not a formula. In fact, it is not essential for you, it only helps you to never misspell and it generates an error.

    Briefly, just go to the cell (D3), go to the DATA tab> Data validation> when opening a window choose the LIST option> choose the correct reference (in this case, the list with all codes)

    To see more, visit this post> https://blog.luz.vc/excel/usando-lista-suspensa-com-validacao-de-dados/

  67. Validation list, how do you do that, is it a formula? If you taught me, I would be very grateful. Thank you for now for your kindness and willingness to help

  68. From what I understand, in the cell D3 is only you to create a list (with data validation) with all the codes that whenever you mark a code, example I 1, it will return you the value of the second column, in this case the description.

  69. I want to enter a code to actually show the product description, a value does not appear

  70. = VLOOKUP (D3; 'DATABASE'! A5: N133; 2; FALSE). You are now looking for products by entering the code in cell D3 in another tab.

  71. Hi Marlon, do 2 things to me, please:

    1 - write the function you are using, exactly as it is written in the spreadsheet
    2 - tell me what is the value that is written in the first argument (sought value)

  72. Yes I did this but did not search the product through the code. My spreadsheet has a product register that extends from cell A5 to N133 and is divided into groups. I have reported in the PROCV function all groups but at the time of entering the code it shows only the first group of products. I do not know what I've done wrong.

  73. Hi, Marlon, everything good?

    the PROCV function works as follows. You define which value you want to fetch (you can use a cell with a list for example) and then define the array where it will search for that value in the first column.

    To search in all groups, your array selection needs to be something like B96: N62 (62 was the last number I saw in the image, but you should put the last one in the last category table.

    Remember that it is important not to have duplicate values ​​in the product list, otherwise the VLOOKUP only finds the first one (marking it as FALSE - exact match)

  74. Good morning, I'm experiencing difficulties with this PROCV function, I made a stock control database and segregated the products into categories according to the image below, but when I inserted the PROCV function it searches only the items listed in the first product group (EX: Lighting and Signaling). How to execute the function so that the search gets in all groups of the database? If possible respond [email protected]

  75. Hi Artur,

    I believe you can only do this in an automated way with VBA because you would need to create a routine that would identify the value found and make a formatted copy of the cell found in that function and paste it into the answer cell.

    Thinking here quickly I could not think of functions or items that were easily applied without VBA.

    Hugs

  76. Is there any way to bring through hyperlink or from PROCV or another function, besides the data formatting of the copied cell? Thank you. Artur Larangeira Filho

  77. Hi Rafael
    I could not generate Macro as you mentioned, would there be a way to do this using formulas? I'm trying to use the shift function, but when I change the numbers the end also changes.
    Thank you

  78. Namaste

    I am grateful for the help. Well, I'm starting now in Excel and I confess out of sheer necessity. I even went looking for some tips on Macros, but I came to the conclusion that my business is Solidworks, Auto CAD, Corel and so on.

    What I mean is to do the following, throw a field: (A1; B1; C1; D1; F1; G1) from a workbook, the six dozen drawn, from Mega. Hence she writes these six dozen in the last set of columns (in 6 case) that I have in another folder. Ex: A3; B3; C3; D3; F3; G3 being in each of these goes a dozen.
    So, I do not have to keep going to the end of the Columns to cast one by one.

    It's that, I started to create a spreadsheet to analyze this game, I know that for many it may seem silly thing, but, the good side, is that I am slowly developing rationale logic and learning Excel. What I again confess was never in my best interest. And I'm enjoying it.
    Very grateful Rafael.
    If you can give me any more help, I promise to give you some good money when I win, lol
    And I believe that.

  79. Hi Ronie,

    you can do this command by recording a macro and attaching it to a button. Basically every time you push the button it will pick up the value that is in A1 and will paste values ​​into the last unfilled cell of column B.

    To do this, follow this step by step:
    1 - start recording the macro
    2 - press CTRL + C in cell A1
    3 - press TAB to move the selected cell to column B
    4 - Press the directional pad down with the CTRL pressed to go to the last value
    5 - Run the command to paste values
    6 - stop recording the macro

    I believe that you will be able to execute this command without problems. Then just create a button and link the macro to it.

    Hugs

  80. Greetings, I would like to know if there is any way I can create a way: when inserting a given data into a cell, eg: A1, it is added to the first empty cell at the end of a given interval,
    and even if I exclude this value from A1, it remains in the cell where it was added.
    Ex: I have a spreadsheet with a column filled with B1: B5000, so to avoid going down to the cell B5001 and add another value, I add it in A1 and it is inserted in B5001 automatically, and if I delete, or add any other value in A1, the previous value is permanently in B5001 and the new value of A1 is inserted and saved in B5002.
    Grato

  81. Hi Gisela,

    I believe you can create an extra spreadsheet for conference. In it you can put 1 field to put the name you are going to fetch and use 3 columns (1 for each database) with a PROCV fetching the name you put.

    In that case, if the name exists in your database you will see the field you want (for quotation for example) and you can create an extra field with the greater or lesser function fetching these 3 values.

    This way you will automatically have the highest or lowest quote value whenever the reference field is filled. Note that PROCV returns you a value based on a reference value. So if you are doing a quote for a product, in the reference field you will put PRODUCT 1, for example, and this value must be written in the same way in the other worksheets. So it is recommended to use data validation (with lists) so that you do not misspell the spelling.

    Hugs

  82. I have 4 spreadsheets. 3 database and 1 result ... I need to identify whether the databases contain services similar to those quoted, which refers to the result spreadsheet. Compare the values ​​with each other and, based on them, insert the lowest value found in the quote sheet. What better formula?

  83. Hi Wagner,

    from what I understand, the problem with PROCV is in the fact that the cell where the control number in the first tab is merged. This causes it to fetch only one value and not all values ​​that are correlated with that cell.

    In addition, PROCv only performs a search, usually taking the first exact value it finds (in case it uses the false argument) or an approximate value (in case it uses true).

    Anyway, I imagine that to look for equal numbers of control numbers, but with other different items you would need to create an auxiliary column to differentiate each of these lines. It could be, for example, a column with the order function.

    Then in the formula you could use the conditional AND linking the PROCV to the control number and a MAJOR function to fetch the sorted items.

  84. Good afternoon. I need help with this function, so it fills in more than one line.
    Ex .: I have 2 guides - the first with the order registration with the code 739, with 3 product lines inserted in it. The second tab is a form where I enter the order code and it returns the data from the first tab automatically. The problem is that in this step only the first product is being filled, when in fact I need all 3 to appear.
    How can I solve this?
    Follow the image of the worksheet.
    Graciously
    Wagner Won Muller Sampaio

  85. Rafael Thanks again (y) worked well here.
    For now, I have no more doubts. If you have any doubts, I'll come to you later. vlw same and all the best.

  86. Hi Gustavo,

    you're welcome, I'm glad to know that it really helped. Regarding this new doubt, it is a common case when we drag the VLOOKUP…

    to fix this (problem # N / A, which in practice is only a visual problem) you can use the SEERCH function - basically it says the following: if the function gives an error, it returns another value). So suppose the function looks like this:

    antes – =PROCV($A$2;P1!$A$13:$R$1000;2;falso)

    then with SEERRO it would be like this - = SEERRO (PROCV ($ A $ 2; P1! $ A $ 13: $ R $ 1000; 2; false); ””)

    To hide the worksheet just right-click it (at the bottom of Excel) and choose the hide option

    Finally this function that he used from SE is the same thing I did now with SEERRO, he created a function to show the cell blank if the result was 0 ok?

    Hugs

  87. Good evening Rafael, I thank you for this force you gave me, thanks to this video can do it right. I've already created my order worksheet.
    I need to ask another question that came up at the conclusion of this task.
    When I drag to copy the formula to all other cells it stays that # N / A and I can not delete it if I do not add the formula. I wanted to know how you can do this and continue with the formula and the white cell.
    you can see that in one of these spreadsheets that I sent you does not appear this but every formula works.
    - and I can hide this spreadsheet, which is the search table, for customers not to touch prices so that it cannot be displayed. just for me?
    - ask me another question can you tell me why in other previous worksheets he made the formulas are they like that with another formula before PROCV ?: = IF (A11 = 0; ””; PROCV (A11; Products! $ A $ 1: $ D $ 500 ; 4; FALSE))

    with Video, everything has been easier, thanks.

  88. Hi Gustavo,

    I recorded a video explaining PROCV using their spreadsheets. As the version of my Excel is the for Mac, it is in English, but I tried to explain everything with the use of the functions in Portuguese. Just to make it even clearer when I use VLOOKUP (= PROCV).

    I hope you understand and if you have any questions just let me know, this is the link:

    https://www.youtube.com/watch?v=Ew02Hq3KN_o&feature=youtu.be

    Hugs

  89. Good morning Rafael, I tried and tried nothing. It's hard for me that I have no basis. I wanted to ask for your help for a bigger help since I do not have so much experience in Excel. It is well explained only half complex for a beginner. in the short term could you explain in a video? I'm sending you some information from the worksheets if you can break this branch for me this time creating for me. and I really want to learn not to depend on other people.

    as I used a spreadsheet with different data and columns may have been what I did wrong.

    I can not pull the formula for all the lines.
    if you can take a look here.

    http://www.mediafire.com/download/9qtun2c36bifiqo/Planilhas+2014.rar

  90. Hi Gustavo,

    I'll try to explain from the beginning here. Unfortunately this year-end is very fast and it will be difficult to set a day to explain exactly how everything works. If you have any doubts, we can make a skype call next year.

    Let's go step by step. Before we begin, let's use some names. Let's use P1 for the info sheet and P2 for the worksheet that does the information search ok?

    Basically, you will have all the data in P1 and in P2, you will put a table with the columns (order, size, description, colors, etc).

    1 - in the order column, you will manually fill in the code number (cell A2) you want to search for information
    2 - in all other columns, you will put the VLOOKUP function, but in each one you will change the third argument (column index number)
    3 - let's go to the first column, the size column. In it we will use the following function:
    =PROCV($A$2;P1!$A$13:$R$1000;2;falso)

    note that I used $ so that when you copy and paste the function, it does not change the reference of the tables. and P1! is the reference for it to pull the values ​​from the worksheet with information. Also, 2 is the index of the column and the false is because you want an exact reference (as explained in the post, look there again if you have not understood here).

    If that succeeds, just copy and paste the function by modifying only the third argument (index column number) for 3, 4, 5, etc. according to the number of columns to the right that you fetch the information

    Once again, I hope I have helped Gustavo. Big hug, merry christmas and a great new year!

  91. Good evening Rafael, I dripped from his explanation but I did not understand his answer. Like this worksheet it works it does search in another worksheet where it has all the information. It has spaces in whites when I put the reference and button to the side already appears the information: size, description and price. When I put the amount in the grid it already multiplies and gives me the subtotal. For me it's a very complex thing I have not been able to do since the beginning. You could contact me by being at WhatsApp email to help me. I need to learn how to make this worksheet so I can stop asking this colleague. It would be great if you talk to me I have the spreadsheet template the spreadsheet with all the data that it should use for the search. I need help Rafael already thank you. ????

  92. Hi, Gustavo, everything good?

    It seems to me a task that you will be able to draw from lyrics. I usually create one more tab for this type of search (to make the spreadsheet more clean and easy to use), but if you want you can do it in that same tab.

    Basically you have to use the PROCV function this way to fetch the value Size:

    = PROCV (cell where you are going to put the code; A13: O1000; 2; false)

    then to the other columns, you only have to modify the column index that in the first case we put 2 and increases to 3, 4, 5 according to the use of columns more to the right (see in the post)

    did you get it? anything is just to tell me hugs

  93. Friend wanted your help to set up a table that I believe is with this formula.
    is a worksheet that looks for results. type I put a reference of a product eg 22001 put pro side and already appears the information of my product, Size, description and value ai has the field of quantity of these product if the product is $ 10,00 if I put the quantity it already multiplies those 10. is a product sales spreadsheet that I need, my friend, who does not have time to do it and I do not want to be asked whenever I need it. I have some here to show you and I wanted to learn how to create mine. I need your help. email: [email protected]

  94. Sincerely, I did not find

    * Respectfully, Luiz Roberto Saraiva *

    *Tels:(0xx5521)99161-1169/98509-4175 *

    * Specialized in Apparel Manufacturing *

    * Bachelor of Business Administration *
    * Graduate in Business Logistics *

    * Click and enter mine *

    *social networks*

    Social Networks [image: LinkedIn] [image:
    YouTube] [image:
    Facebook] [image:
    Blogger] [image:
    Twitter] Contact me: [image: Skype] lrsaraiva [image: MSN] [email protected]
    Designed with WiseStamp - Get yours

    In 1's August 2014 09: 35, Disqus wrote:

  95. Hi Luiz!

    It's OK? In fact, your other comment was answered, yes. I had sent you the link of our category of posts about Excel: blog.luz.vc/c/excel/

    And also asked the 3 most important points to guide you calmly. I hope you can see this answer here!

    Hugs

  96. I have a lot of respect for your work.but where can you tell me this to go deeper.I already posted and did not have [email protected]

    PURPOSE

    To train
    the student in the use of Excel program for application in situations and problems
    typical of Production Management, Quality Management and Finance
    Business.

    SPECTATORS
    TARGET

    Undergraduate students or
    recent graduates in Engineering, Administration, Economics, Accounting,
    Actuarial and areas to ends;

    Management Professionals.

    EVENTS
    SUMMED UP

    Solutions
    by Simulation:

    Maintenance plans;

    Calculation of probability;

    Targeting system simulation
    maximum profit;

    Calculation of fleet of
    vehicles;

    Maintenance plans;

    Stock Policies
    (periodic and permanent control).

    Solutions by Linear Programming (SOLVER):

    Logistics problems (route
    and location);

    Choice of production mix
    for maximum profit or minimum cost;

    Manufacturing plan;

    Planning Fronts and
    scale of labor;

    Integrated planning of
    production

    Quality
    analysis - elaboration of control charts

    Visual
    Basic

    Macro recording;

    Relative Reference x
    Absolute Reference;

    Structure of a macro;

    Creating a function, function
    defined by the user;

    Financial functions;

    Inbox / Outbox
    message;

    Control structures (if-then-else; else
    if; do while / until);

    Practical applications
    spreadsheet formatting and data processing;

    Tools
    Supplemental Excel

    Text for columns

    Scenarios

    Dynamic table

    Dynamic chart

    Conditional Formatting

    Basic formulas of text,
    date and time, statistics, logical and financial

    Works with worksheet
    (password, hide, group, ungroup and freeze panels)

    Goal achievement

  97. Good evening Leandro, I'm a beginner and I would like to learn and have knowledge about these worksheets with the procv, proch, somase and so many functions that are important for our day to day work, but their material was useful and of great value to me, thank you.

  98. Very good, you could take a doubt I made a table to organize films, but I only find the film if the name is correct, if I lack an accent I can not find.

  99. Congratulations… Can you tell us about formulas within formulas, eg: = Procv (……); add up (….))

Comments are closed.