ROW questions

The INDEX function returns the value of a cell in a specified row and column within a range. For example, =INDEX(A1:C10, 2, 3) returns the value from the second row and third column in the range A1:C10. This function is useful for retrieving specific data points from a table.

The OFFSET function can be used to create dynamic ranges by adjusting its reference based on specified rows and columns. For example, =OFFSET(A1, 2, 3, 5, 5) creates a range starting 2 rows down and 3 columns over from A1, with a height of 5 rows and a width of 5 columns. This is useful for creating dynamic named ranges or adaptable formulas.

The VLOOKUP function searches for a value in the first column of a table and returns a value from a specified column in the same row. For example, =VLOOKUP('Apple', A1:C10, 2, FALSE) searches for 'Apple' in column A and returns the corresponding value from column B. This function is useful for looking up information in tables.

The LOOKUP function searches for a value in one row or column and returns a value from the same position in a second row or column. For example, =LOOKUP(10, A1:A10, B1:B10) looks for the number 10 in A1:A10 and returns the corresponding value from B1:B10. This function is useful for simple lookups and data retrieval.

To find duplicate rows, use a query with a `GROUP BY` clause and `HAVING` to identify duplicates. For instance: `SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;`. To remove duplicates, you might use a `DELETE` statement with a subquery. For example: `DELETE FROM table_name WHERE ctid NOT IN (SELECT MIN(ctid) FROM table_name GROUP BY column_name);`.