VLOOKUP FunctionThe VLOOKUP function means "vertical lookup". Its purpose is to look for some data or value in an array and find its corresponding match.Syntax of the VLOOKUP Function=VLOOKUP(lookup_value; table_array; col_index_num; range_lookup)Where:- lookup_value is the argument you want to provide as the basis for the search;- table_array is the range where the search will be performed;- col_index_num is the column from which you want to obtain the result, considering that columns are counted from the range stipulated in table_array- range_lookup is the search precision, which can be exact or approximate to the desired value. It can therefore be TRUE or FALSE. If it is FALSE, it returns the value only if the lookup value exactly matches the table content.Details to pay attention to:-The function only looks to the right- The function always returns the first match- It is not case-sensitive. "A" and "a" are seen as the same letter.ExampleSalespersonRegionSalesSalesperson 1SouthR$ 87.925,00Salesperson 2NorthR$ 129.887,00Salesperson 3SoutheastR$ 12.908,00Salesperson 4SouthR$ 99.087,00Salesperson 5NortheastR$ 234.987,00Salesperson 6NorthR$ 10.000,00Salesperson 7SouthR$ 105.000,00Salesperson 8MidwestR$ 1.876.098,00Salesperson 9NortheastR$ 12.387,00Salesperson 10SouthR$ 12.876,00Salesperson 11SoutheastR$ 123.865,00Salesperson 12MidwestR$ 128.543,00Salesperson 13NorthR$ 987.000,001. How much did Salesperson 7 sell?Solution1. =VLOOKUP("Salesperson 7"; A1:D14; 3; FALSE) = R$ 105.000,00HLOOKUP FunctionThe HLOOKUP function means "horizontal lookup". Its purpose is to look for some data or value in an array and find its corresponding match.Syntax of the HLOOKUP Function=HLOOKUP(lookup_value; table_array; row_index_num; range_lookup)Where:- lookup_value is the argument you want to provide as the basis for the search;- table_array is the range where the search will be performed;- row_index_num is the row from which you want to obtain the result, considering that rows are counted from the range stipulated in table_array- range_lookup is the search precision, which can be exact or approximate to the desired value. It can therefore be TRUE or FALSE. If it is FALSE, it returns the value only if the lookup value exactly matches the table content.ExampleNameAgeSalaryRenato251500Eduardo231800Cristina361200Bárbara381750Vera221900Evandro241600Fátima312100Carolina271990Eloisa3013001. What is the salary of the third person in vertical order?Solution1. =HLOOKUP("Salary"; A1:C10; 4; FALSE)XLOOKUP FunctionThe XLOOKUP function was created to replace VLOOKUP, bringing more practicality and eliminating several limitations.With it, you can look up values to the left or right, up or down, as well as allow clearer results when nothing is found.Syntax of the XLOOKUP Function=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])Where:- lookup_value → value to be searched.- lookup_array → range where the value will be searched.- return_array → range from which the result will come.- if_not_found (optional) → message or value returned if no match is found.- match_mode (optional) → can be 0 (exact), -1 (next smaller), 1 (next larger), or 2 (wildcards such as * and ?).- search_mode (optional) → allows you to define the search direction (from first to last or from last to first).Advantages over VLOOKUP- Allows you to look up values in any direction (not only to the right).- Does not require the lookup column to be at the beginning of the table.- Allows you to define custom messages if nothing is found.- It is simpler to read and understand.Example with the previous table1. How much did Salesperson 7 sell?Solution=XLOOKUP("Salesperson 7"; A2:A14; C2:C14; "Not found")- lookup_value: "Salesperson 7"- lookup_array: A2:A14 (salesperson column)- return_array: C2:C14 (sales column)- if_not_found: "Not found"ExercisesSalespersonRegionSalesJoãoSouth85500MariaNorth120300PedroSoutheast15200AnaSouth98000CarlosNortheast250000JulianaNorth9800RobertoSouth110000BeatrizMidwest1890500FernandaNortheast13500RafaelSouth142001. What is Carlos's region?2. What is Rafael's sales value?3. Which salesperson made the highest sale?4. What is the total sales of the salespeople in the South region?NameAgeSalaryEduardo261800Carolina292100Gustavo352500Larissa403000Fernanda241900Thiago312700Bruna282200Marcos372900Aline3326005. What is Carolina's age?6. What is Marcos's salary?7. Who is the oldest employee?8. What is the average salary of employees over 30 years old?CodeProductCategoryUnit PriceStock101MousePeripheralsR$ 80,00120102KeyboardPeripheralsR$ 150,0085103MonitorHardwareR$ 1.200,0045104ProcessorHardwareR$ 2.500,0020105Graphics CardHardwareR$ 3.800,00151061TB SSDStorageR$ 700,00501072TB HDDStorageR$ 500,006010816GB MemoryHardwareR$ 400,00100109750W Power SupplyHardwareR$ 600,0040110Gaming CasePeripheralsR$ 450,00259. What is the unit price of the product with code 104?10. How many units are available of the product "16GB Memory"?11. Which category does the product with code 110 belong to?12. What is the most expensive product in the table?13. What is the average stock of products in the "Hardware" category?
Connectez-vous et rejoignez la conversation
— commentaires 0
, Réactions 1
Soyez le premier à commenter