Look Ups in Excel

We all use Excel, and searching for data from another sheet or Excel file is unavoidable. This is where you begin to become an advanced user. One of the fundamental skills of an advanced user is performing lookups.

In Excel, there are four types of lookups:

1. VLOOKUP: We use this formula when we need to search for something in a column (a vertical object) by moving vertically down through each row.

2. HLOOKUP: If you need to search for something in a row, this is when we use HLOOKUP. It allows us to search for a value across a row (a horizontal object) through each column.

3. XLOOKUP: Many of us have been frustrated with VLOOKUP at some point, as it tends to be overly optimistic and doesn't look back. However, sometimes you need to rely on your past (ex)perience. That's when we turn to XLOOKUP. Jokes aside, once you start using XLOOKUP, it's hard to return to VLOOKUP.

4. Lookup: This is the basic version of lookup, it does what it says, looks for a value in a defined manner.

VLOOKUP

Definition: VLOOKUP locates a value in the initial column of a specified range and retrieves a corresponding value from a designated column within the same row.

Yeah that's what google says, lets make it simple a bit.

So V in VLOOKUP is vertical. Now if you expand it, it simply means Vertical Look Up, i.e. you are looking for a value vertically.

Lets looks at VLOOKUP formula and break it down.

=VLOOKUP(lookup value, table_array, col_index, [range_lookup])

Ok now what in the world is all these things.

Lets say, I live at the top floor of my building and I ordered a pizza. I get a message that it got delivered but wait what? I did not get my pizza.

So now what do we do? We look for Pizza. Now our formula looks like

=VLOOKUP("Pizza", table_array, col_index, [range_lookup])

We know he is somewhere inside the building but not sure which floor. For simplicity lets just assume there is only one flat per floor (that is our column number), yeah we rich.

=VLOOKUP("Pizza", "This Building", 1, [range_lookup]) 

Now when you call up and ask, you can ask "have you received a pizza?" (this is ) or you can say "have you received a pizza ordered on my name?"

=VLOOKUP("Pizza", "This Building", 1, "have you received a pizza ordered on my name?") 

So, from one of the houses you will get a response as "Yes, I have". That is your VLOOKUP result.

Now in technical terms, it will look like this.

=VLOOKUP("Pizza", Sheet2!A1:D50, 3, False)

So we are looking for a value called "Pizza" in range A1:D50 that exists in Sheet2. If we find a match in column A of Sheet2 range A1:D50, I want to get the return value from column C. And finally, no i don't want someone order who happened to also order pizza at same time hence "False".

To make it even more dynamic, you can replace static value "Pizza" with a cell address and it will search for the value of the cell address.

HLOOKUP

Definition: HLOOKUP is beneficial for retrieving information from a dataset organized in rows instead of columns.

You know that is from google.

Now H is for Horizontal, and it means its Horizontal Look Up.

Lets look at the formula and break it down.

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

Lets say you stay in a independent villa/house, but there are many villas in the same society.

One of my pet ran out and his name is Buddy, man he is a naughty one.

=HLOOKUP("Buddy", "This society", 1, "Have you seen buddy?")

So here we are calling up people and asking if they have seen buddy.

Now lets look at it technically.

=HLOOKUP("Buddy", "Sheet2!A1:D20", 15, False)

Now again we are looking for a value "Buddy" inside range A1:D20 of sheet2 and returning a value that exists in row 15 of range A1:D20 of sheet2 lets say, that is "Red".

Column Row	A	B	C	D
1	Name	Poodles	Cookie	Buddy
2	Type	Bird	Cat	Dog
...	...	...	...	...
15	Collar Color	blue	black	red

To make it even more dynamic, you can replace static value "Buddy" with a cell address and it will search for the value of the cell address.

XLOOKUP

Definition: 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. 

X is for I don't really think X stands for anything at all, but lets assume it means eXpert LookUP.

Why am I calling it expert, because it can work as both VLOOKUP and HLOOKUP.

How?

Lets look at the syntax.

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

Well the syntax should be clear now, it looks almost same as the other two.

Lookup value is what you want to look for.

Lookup array unlike VLOOKUP and HLOOKUP, it is one array where you want to search for the value.

If your lookup_array is a row, lets say A5:K5 then it will work as HLOOKUP. If you Lookup_array is a column, lets say D1:D10 then it will work as VLOKKUP.

Return array is what you want back, so if you are using it as HLOOKUP, then your return array must be a row, lets say we want to return A1:K1.

If you notice, like HLOOKUP we are looking for a value and returning a value from another row, but unlike HLOOPUP, we are returning a row that is above the lookup_array i.e. looking at 5th Row and returning 1st Row value.

Similarly, VLOOKUP too can return values from any column of selection, be it on left or right side of Lookup_array.

Another benefit is that unlike V/HLOOKUP, you do not need to select whole range and count the row/column number you want to return. In XLOOKUP you can simply select the columns/rows to look at and return.

LOOKUP

LOOKUP is a very basic function in excel. it does exactly what it name suggest. Looks for a value in a defined row/column.

Similar to XLOOKUP, based on what you define whether row or column it can look for the value in that row/column.

Lets look at its syntax.

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Lookup_value : what you want to look for.

Lookup_vector: this the row/column where you want to look the value at. Please note this column has to be sorted.

Result_vector: This is the corresponding row/column that you want to return.

Now, lets get into where it gets interesting.

Where would you use this formula? Well this is a good one when you want to get results based on approx. match, good for math/finance.

lets say we have classified results into following categories.

Column Row	A	B
1	Marks	Grade
2	0	F
3	35	E
4	50	D
5	70	C
6	90	B
7	100	A

So lets say, minimum marks required for a grade. example, 35 is the minimum marks for grade E and 70 is the minimum requirement for getting a grade C and so on.

You can simply use lookup.

Lets try, LOOKUP(68, A2:A6, B2:B6). What do you think the grade would be?

The result will be grade D.

Well for this scenario our Marks were sorted in Ascending order.

Well this brings us to the end of Look up. Hope this blog is able to give you some insights into how these look ups works.