search

 Excel offers a variety of search functions that allow you to locate and retrieve data within your spreadsheets. Below are 10 expanded examples of Excel search functions, including both formulas and data usage scenarios:

---

1. VLOOKUP (Vertical Lookup)

• Purpose: Searches for a value in the first column of a table and returns a value in the same row from a specified column.

• Formula:

  excel

  Copy

  =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Example:

  excel

  Copy

  =VLOOKUP("Apple", A2:B10, 2, FALSE)

  • Searches for "Apple" in column A and returns the corresponding value from column B.

---

2. HLOOKUP (Horizontal Lookup)

• Purpose: Searches for a value in the first row of a table and returns a value in the same column from a specified row.

• Formula:

  excel

  Copy

  =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• Example:

  excel

  Copy

  =HLOOKUP("Q1", A1:D3, 2, FALSE)

  • Searches for "Q1" in the first row and returns the corresponding value from the second row.

---

3. INDEX + MATCH (Flexible Lookup)

• Purpose: Combines INDEX and MATCH to perform a more flexible lookup than VLOOKUP or HLOOKUP.

• Formula:

  excel

  Copy

  =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Example:

  excel

  Copy

  =INDEX(B2:B10, MATCH("Orange", A2:A10, 0))

  • Finds "Orange" in column A and returns the corresponding value from column B.

---

4. XLOOKUP (Modern Lookup)

• Purpose: A powerful replacement for VLOOKUP and HLOOKUP that works in any direction.

• Formula:

  excel

  Copy

  =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• Example:

  excel

  Copy

  =XLOOKUP("Banana", A2:A10, B2:B10, "Not Found", 0, 1)

  • Searches for "Banana" in column A and returns the corresponding value from column B. If not found, it returns "Not Found".

---

5. SEARCH (Case-Insensitive Text Search)

• Purpose: Finds the position of a substring within a text string (case-insensitive).

• Formula:

  excel

  Copy

  =SEARCH(find_text, within_text, [start_num])

• Example:

  excel

  Copy

  =SEARCH("apple", A2)

  • Returns the position of "apple" in the text in cell A2.

---

6. FIND (Case-Sensitive Text Search)

• Purpose: Finds the position of a substring within a text string (case-sensitive).

• Formula:

  excel

  Copy

  =FIND(find_text, within_text, [start_num])

• Example:

  excel

  Copy

  =FIND("Apple", A2)

  • Returns the position of "Apple" in the text in cell A2 (case-sensitive).

---

7. FILTER (Dynamic Array Filtering)

• Purpose: Filters a range of data based on specified criteria.

• Formula:

  excel

  Copy

  =FILTER(array, include, [if_empty])

• Example:

  excel

  Copy

  =FILTER(A2:B10, B2:B10 > 100, "No Data")

  • Filters rows in A2:B10 where column B values are greater than 100. If no matches, it returns "No Data".

---

8. LOOKUP (Vector Form)

• Purpose: Searches for a value in a single row or column and returns a value from the same position in another row or column.

• Formula:

  excel

  Copy

  =LOOKUP(lookup_value, lookup_vector, result_vector)

• Example:

  excel

  Copy

  =LOOKUP("Grapes", A2:A10, B2:B10)

  • Searches for "Grapes" in column A and returns the corresponding value from column B.

---

9. CHOOSE (Index-Based Lookup)

• Purpose: Returns a value from a list based on a specified index number.

• Formula:

  excel

  Copy

  =CHOOSE(index_num, value1, value2, ...)

• Example:

  excel

  Copy

  =CHOOSE(2, "Apple", "Banana", "Cherry")

  • Returns "Banana" (the second value in the list).

---

10. IF + ISNUMBER + SEARCH (Conditional Search)

• Purpose: Combines IF, ISNUMBER, and SEARCH to check if a substring exists in a cell.

• Formula:

  excel

  Copy

  =IF(ISNUMBER(SEARCH(find_text, within_text)), "Found", "Not Found")

• Example:

  excel

  Copy

  =IF(ISNUMBER(SEARCH("apple", A2)), "Found", "Not Found")

  • Checks if "apple" exists in cell A2 and returns "Found" or "Not Found".

---

Bonus: UNIQUE + FILTER (Advanced Filtering)

• Purpose: Filters unique values based on criteria.

• Formula:

  excel

  Copy

  =UNIQUE(FILTER(array, include))

• Example:

  excel

  Copy

  =UNIQUE(FILTER(A2:B10, B2:B10 > 100))

  • Filters and returns unique rows where column B values are greater than 100.

---

These functions can be combined and expanded to handle complex data retrieval and analysis tasks in Excel. Let me know if you need further clarification or examples!