Physical Address
Haryana ,India
Lookup and reference functions serve as navigational beacons in vast seas of data, allowing you to pinpoint and extract precise information from sprawling tables or ranges under particular stipulations. For data analysts immersed in monumental datasets, these formulas are not mere conveniences — they are indispensable instruments of precision.
1. VLOOKUP() – Vertical Lookup
Purpose: Retrieves a value from a different column but in the same horizontal alignment, after scouring the primary column of a designated range for the desired criterion.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Illustration:
Suppose column A houses product identifiers while column B stores their respective prices:
=VLOOKUP(101, A2:B10, 2, FALSE)
This incantation unveils the price tagged to the product bearing ID 101.
2. HLOOKUP() – Horizontal Lookup
Purpose: Mirrors the essence of VLOOKUP but prowls horizontally, scanning across the initial row rather than down the first column.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Illustration:
If the inaugural row lists product names and the second row holds their costs:
=HLOOKUP(“Apple”, A1:F2, 2, FALSE)
This expression summons the price associated with “Apple.”
3. INDEX() – Retrieve by Row and Column Coordinates
Purpose: The function is to display the precise value that is present in a cell at a given row-column intersection.
Syntax:
=INDEX(array, row_num, [column_num])
Illustration:
=INDEX(B2:D10, 3, 2)
Extracts the datum stationed at row 3, column 2 within B2:D10.
4. MATCH() – Locate Position of a Value
Purpose: Reveals the ordinal position of a sought-after value within a specified range, abstaining from returning the value itself.
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])
Illustration:
=MATCH(50, A2:A10, 0)
Discloses the row location where the number 50 resides within A2:A10.
5. INDEX + MATCH – The Versatile Alliance
Why prefer it? This union outshines VLOOKUP’s rigidity, as it navigates in both vertical and horizontal dimensions without the constraint of having the lookup column positioned foremost.
Illustration:
=INDEX(C2:C10, MATCH(101, A2:A10, 0))
Discerns the location of Product ID 101 in A2:A10, then fetches the corresponding entry from C2:C10.
6. XLOOKUP() – The Contemporary Successor
Purpose: Scans for a target in a given range and yields a corresponding value from another range — effortlessly functioning across vertical or horizontal axes.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Illustration:
=XLOOKUP(101, A2:A10, C2:C10, “Not Found”)
Searches for 101 in A2:A10, retrieves the linked value from C2:C10, and if the quarry eludes discovery, returns “Not Found.”
7. LOOKUP() – The Veteran Operator
Purpose: The function’s purpose is to gracefully seek a value in a single row or column and return the companion value from an aligned location in a second range, whatever its age.
Syntax:
=LOOKUP(lookup_value, lookup_vector, result_vector)
Illustration:
=LOOKUP(101, A2:A10, C2:C10)
Seeks 101 in A2:A10 and delivers the parallel entry from C2:C10.
8. CHOOSE() – Selector of Options
Purpose: Employs an index numeral to pluck a specific value from a curated list.
Syntax:
=CHOOSE(index_num, value1, [value2], …)
Illustration:
=CHOOSE(2, “Apple”, “Banana”, “Orange”)
Presents “Banana” as the chosen fruit.
9. ADDRESS() – Generate a Cell’s Coordinates
Purpose: Using the specified row and column integers, this formula returns the cell’s reference as an absolute-textual address.
Syntax:
=ADDRESS(row_num, column_num)
Illustration:
=ADDRESS(3, 2)
Responds with $B$3.
10. ROW() & COLUMN() – Numerical Identity of Cells
- =ROW(A5) → Elicits the number 5, signifying the row position of A5.
- =COLUMN(C2) → Produces 3, denoting the column index for C.
Analyst’s Insight: For multi-directional lookups, sidestep the dated VLOOKUP and HLOOKUP. Opt for XLOOKUP or the INDEX + MATCH tandem — their agility and adaptability will save both time and headaches.
