📊 Curso Google Sheets ✅ Búsqueda vertical y búsqueda horizontal (BUSCARV y BUSCARH)

Google Sheets: Vertical and Horizontal Lookup Functions

Introduction to Google Sheets Functions

• The video introduces vertical and horizontal lookup functions in Google Sheets, using a previously worked spreadsheet titled "búsquedas."

• The context involves two lists: one of countries by population and another of countries with their capitals, imported from CSV files.

Understanding the Vertical Lookup Function

• The vertical lookup function allows users to search for a value in the first column of a range; for example, searching for a country name.

• Upon finding a match (e.g., Nigeria), users can specify which data to retrieve from that row, such as the country's population or other details.

Implementing the Function

• To add population data next to each country and capital, the user will utilize the vertical lookup function in column F.

• The function requires several parameters:

• Search value (country name).

• Data range (columns A and B containing relevant information).

• Index number indicating which column's data to return (1 for country name, 2 for population).

Setting Up Parameters Correctly

• It is crucial to select all necessary columns when setting up the range; otherwise, retrieving specific data like population won't be possible.

• Users must indicate whether the list is sorted; since it’s sorted by population but not alphabetically, they should set this parameter to false.

Copying Formulas with Absolute References

• To apply formulas across multiple rows without changing references incorrectly, absolute references (using $) are essential. This ensures consistent referencing during formula copying.

• After adjusting references correctly in the formula, users can drag down from the corner of the cell to apply it across all relevant rows efficiently.

Troubleshooting Errors

Sorting and Finding Data in Excel

Sorting Data by Country Names

• The process begins with right-clicking to sort the interval, keeping the default settings which sorts countries alphabetically.

• An example is given with "Bosnia, C, Govina," highlighting a discrepancy in naming conventions (hyphen vs. Greek letter), leading to search failures.

Correcting Errors in Population Data

• To rectify errors, copying the correct name and pasting it allows for accurate population retrieval; Bosnia's population is confirmed as 3,500,000.

• If a country like Vatican City isn't found in the list, it indicates an error that needs addressing.

Grouping Tables and Horizontal Searches

• The speaker removes unnecessary columns after grouping two disordered tables and incorporating new data.

• A horizontal search method is introduced by duplicating the sheet containing country populations for better organization.

Transposing Data for Better Layout

• The speaker demonstrates how to transpose data from columns to rows using the TRANSPOSE function in Excel.

• By selecting a range of cells instead of individual ones, all relevant data can be transposed efficiently.

Implementing Horizontal Lookup Functions

• Inserting population data into row six involves using horizontal lookup functions (HLOOKUP), maintaining similar parameters as vertical lookups but focusing on rows.

• The steps include defining what value to search for (country name), specifying the range of data including both country names and populations.

Finalizing Data Retrieval Process

• After setting up HLOOKUP correctly with necessary parameters, results are verified against known population figures.

• It’s noted that some countries may still not be found due to previous errors; however, corrections made earlier ensure accurate results moving forward.

Conclusion on Lookup Functions