Horizontal & Vertical Look-up 📄

Can you do Vertical & Horizontal Look-up at the same time in Excel? The Answer is Yes!. Let us discuss how?

In our day-to-day Excel works, we may come across situations where we require the value which is an intersection of both Horizontal & Vertical look-up. In simple words, we require the combination of VLOOKUP & HLOOKUP in a single cell. Look into the below image for your quick reference of the scenario described above:

Post Feature

There are two ways to solve the above scenario, which are

1. INDEX & MATCH Functions (Available on both M365 & Other Office packs)

2. XLOOKUP Function (Available only on M365)

Note: M365 means Microsoft 365 Subscription based office suite.

1️⃣ INDEX & MATCH

Before proceeding to the solution, let us first understand the Syntax structure and basic functionality of these two functions.

INDEX Function:

The INDEX function returns a value or the reference to a value from within a table or range.

INDEX Function with Syntax

Syntax (Array Formula):

1. array: similar to table array reference in the VLOOKUP function

2. row_num: position of the row in an array from which to return a value

3. column_num: position of the column in an array from which to return a value

INDEX Function with Use Case

In the above scenario, We require to extract the value 471, therefore first we selected the range of the table in an array, inserted 5 for row_num (Singh, Nan positions in 5th row), and gave 3 for column_num (C positions in 3rd column). INDEX Function picks the intersection of the 5th row and 3rd column in the selected range of cells and returns the value 471 which is available at the inspecting cell D6.

MATCH Function:

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

MATCH Function with Syntax

Syntax:

1. lookup_value: similar to the lookup_value syntax reference in the VLOOKUP

2. lookup_array: similar to the lookup_array syntax reference in the XLOOKUP (or) range of cells in which lookup_value has to be looked into

3. match_type [Optional]: 0 Exact Match (Default if not specified); 1 less than and -1 greater than

MATCH Function Example (Vertical— Row)

MATCH Function Example (Horizontal — Column)

In the above scenario, we have mentioned Singh, Nan & C in lookup_value, specified the range of cells for Name & Grade respectively in lookup_array, and given 0 for match_type (Exact Match). MATCH Function returns us the position of both Singh, Nan & C in their row & Column.

If you have noticed carefully output of the MATCH function is typically an input for INDEX Function which leads us to the final explanation which is horizontal & vertical lookup.

The combined effect of INDEX & MATCH 💥

In the above two examples, We got position №5 of Singh, Nan through the Row MATCH function & position №3 of C through the Column MATCH function which we have manually inserted in the 1st INDEX function example.

In simple words, incorporate the above 2 MATCH functions inside the existing INDEX function to the intersection of horizontal and vertical look-up.

INDEX & MATCH Function Example

Extended Image for reference

2️⃣ XLOOKUP Function

The XLOOKUP function is gaining more traction in recent years because of its versatility & usage. The XLOOKUP function searches a range or an array and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. Before going into the solution, let us first discuss the syntax of the function.

XLOOKUP Function with Syntax

Syntax:

1. lookup_value: similar to the lookup_value syntax reference in the VLOOKUP

2. lookup_array: range of cells in which lookup_value has to be looked into

3. return_array: range of cells which has to be returned (if a valid match is found)

4. if_not_found [Optional]: “Value” to be returned if a valid match is not found

5. match_mode [Optional]: 0 Exact match (If none is found, return #N/A; This is the default); -1 Exact match (If none are found, return the next smaller item); 1 Exact match (If none are found, return the next larger item), and 2 a wildcard match where *, ? & ~.

6. search_mode [Optional]: 1 Perform a search starting at the first item (This is the default); -1 Perform a reverse search starting at the last item; 2 Perform a binary search that relies on lookup_array being sorted in ascending order (If not sorted, invalid results will be returned), and -2 Perform a binary search that relies on lookup_array being sorted in descending order (If not sorted, invalid results will be returned).

Note: Probably you got confused while reading the last two syntaxes (match_mode & search_mode), don’t worry those are optional fields and useful in advanced computations. For our current scenario, those are not required.

XLOOKUP Function Example

Extended Image for reference

In the above example, we have used two XLOOKUP functions one (horizontal) inside the other (vertical).