Microsoft Excel - working with excel's lookup function

📘 Working with Excel Lookup Functions

🔹 Microsoft Excel VLOOKUP() Function

VLOOKUP always searches in the first (leftmost) column of the table_array

It then returns a value from a column to the right of that first column

Widely used but has limitations (left-to-right only): “Left-to-right only” means VLOOKUP can return values only from columns that are to the right of the lookup column, never from the left.

Syntax:

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

📌 Best for simple vertical lookups

🔹 Microsoft Excel HLOOKUP() Function 

Searches for a value in the first row of a table

Returns data from rows below

Less commonly used than VLOOKUP

Syntax:

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

📌 Best for horizontally structured data

🔹 Microsoft Excel INDEX() Function 

Returns a value from a cell based on row and column number. Very powerful and flexible

Syntax:

=INDEX(array, row_num, [column_num])

📌 Does not perform lookup by itself

🔹 Microsoft Excel MATCH() Function 

Finds the position of a value in a row or column

Often used with INDEX

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

📌 Returns position, not the value

🔹 INDEX() + MATCH() Combined

A powerful alternative to VLOOKUP

Can lookup left, right, up, or down

Example:

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

📌 More flexible and efficient than VLOOKUP

🔹 Dynamic HLOOKUP() using MATCH() — Advanced

MATCH dynamically identifies the row number

Prevents formula breakage when structure changes

Example:

=HLOOKUP(A1, A1:D10, MATCH("Sales", A1:A10, 0), 0)

📌 Makes HLOOKUP adaptable and robust

✅ Summary Table