Microsoft Excel’s VLOOKUP function is a more dynamic way of looking up data in the application. Thus, the user can find different items in a table or range by rows. For example: in an automotive parts inventory spreadsheet, you can search by part number to see information such as name, price, manufacturer, etc. With that, this function turns out to be quite important. Learn how to do PROCV🇧🇷
What is the VLOOKUP formula in Excel
THE VLOOKUP function has a formula that has the following structure:
PROCV(valor_procurado;matriz_tabela;núm_índice_coluna;procurar_intervalo)in which:
- lookup_value: is the value you want to find;
- table_array: is the range where the lookup value is located. This table or range can have two or more columns, as well as be located on another worksheet;
- column_index_num: the column number that contains the value to be returned. For example: if the range is B3:E12, you would count column B as 1, column C as 2, and so on;
- range_search: value that determines whether the search will be made for an exact or approximate match of the searched value, being FALSE for exact search and TRUE for approximate. The default value is TRUE.
In addition, there is a more simplified formula released by Microsoft itself:
=VLOOKUP(What you want to search for, where you want to search, the column number in the range that contains the value to return, returns an Approximate or Exact match – indicated as 1/TRUE or 0/FALSE).
How to Use the VLOOKUP Function in Microsoft Excel
In our example of an automotive parts price spreadsheet, using the PROCV function, you can configure a search field by part code that will return information such as name, price and manufacturer.
- Insert the searched value cell into the formula
First, add the “=” sign and reference the cell that has the value you want to look up. In our example, we will use C7. The formula will be inserted in the field where you want to display the returned value, not in the field where the search term will be entered.
- Determine the table_array of your VLOOKUP
In this step, you need to inform the worksheet range where your result will be searched. In our example, it will be A1:D5;
- Enter column_index_num
In this step, inform which column is what you want to search. Remembering that it starts to be counted from the first column that you put in matrix_table. Thus, in B3:E12, column B will be 1, column C will be 2, and so on;
- Let us know if you want an exact or approximate search
If you want Excel to return the exact value you are looking for, enter 0 (or TRUE). If you are looking for approximate values, type 1 (or FALSE). In our example, we use 0;
- do your research
Finally, you can use the formula to perform searches in your worksheet. You can just use the code for each item to find the results.
In short
We use the following formula:
=PROCV(C7;A1:D5;2;VERDADEIRO) in which:
- C7 is the cell where the part code will be inserted;
- A1:D5 is the range of the table with the parts information;
- two is the number of the column containing the information to be returned, with column A number 1, column B number 2, and so on;
- REAL is the parameter to perform the search for approximate values;
To display the information in the other cells, simply copy the formula changing the corresponding values such as the column number (3 to return the price and 4 to return the manufacturer).
If you prefer to use the autocomplete feature, don’t forget to “lock” the range of cells by adding the “$” in front (example: $A$1:$D$5 will make the range not change when dragging the formula from one cell to another).
As with other Excel functions, such as IFthe VLOOKUP function can also be used alongside other functions in a formula, providing even better tools for you to automate or enhance your worksheets.
How to use VLOOKUP to pull data from other tabs in the same worksheet
- To start, select a column in one of the tabs that will be the result of your search;
- Locate the reference unit code and enter =VLOOKUP(looked-up_value;
- Then open another tab and select all the cells you want to use in the data search. Don’t forget to add the “;” in the VLOOKUP formula;
- Define the column that has the information to be returned;
- Press “Enter” and that’s it.
What to do when PROCV does not work?
Even following all the steps correctly, it is possible that something goes wrong. Don’t despair, there are ways to fix and tweak your spreadsheet.
Check if lookup value is in first column given in formula
It’s important to make sure the value you’re interested in is in the right place. VLOOKUP has a restriction that it can only look up values in the leftmost column in the table array. That is, ensure that the formula is referencing the correct column.
Check that there are no hidden spaces or non-printing characters in the worksheet
Inadvertently, we can end up typing something that shouldn’t be in the worksheet or in the formula itself. So, if the desired result isn’t coming up, it might be a good idea to check each cell for any hidden spaces or a non-printing character.
See if there is a value less than the entered search value
This can occur with the range_lookup argument when searching with the TRUE setting. If the #N/A error appears, the lookup value is less than the smallest value in the array. If you come across this glitch, just correct the number as needed.
Make the search column in ascending order
When using the VLOOKUP function and the TRUE argument, an error may arise if one of the lookup columns is not in ascending order. The first possible solution is to change the formula to FALSE as it does not require the use of a sort.
round big numbers
Because Excel has trouble storing large floating-point numbers, you have to shorten and round each one to five decimal places.
With information: Microsoft
https://tecnoblog.net/responde/usar-procv-microsoft-excel/