What is Vlookup and Hlookup in Excel with example

VLOOKUP allows you to search a data range that is set up vertically. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns. LOOKUP and related functions are commonly used for business analytics in Excel as a way of slicing and dicing data for analysis.

What is Hlookup in Excel with example?

AxlesBearingsBolts=HLOOKUP(“B”, A1:C4, 3, TRUE)Looks up “B” in row 1, and returns the value from row 3 that’s in the same column. Because an exact match for “B” is not found, the largest value in row 1 that is less than “B” is used: “Axles,” in column A.5

What is VLOOKUP formula?

In its simplest form, the VLOOKUP function says: … =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

How use VLOOKUP formula in Excel with example?

lookup_value – The value to look for in the first column of a table.
table_array – The table from which to retrieve a value.
column_index_num – The column in the table from which to retrieve a value.
range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

What VLOOKUP is used for?

When you need to find information in a large spreadsheet, or you are always looking for the same kind of information, use the VLOOKUP function. VLOOKUP works a lot like a phone book, where you start with the piece of data you know, like someone’s name, in order to find out what you don’t know, like their phone number.

What is the difference between VLOOKUP Hlookup and index match?

The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. … INDEX MATCH allows you to click to choose which column you want to pull the value from. This leads to fewer errors.

What is macro in Excel?

If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. … You can create and then run a macro that quickly applies these formatting changes to the cells you select.

How do I match data in Excel?

Select the entire data set.
Click the Home tab.
In the Styles group, click on the ‘Conditional Formatting’ option.
Hover the cursor on the Highlight Cell Rules option.
Click on Duplicate Values.
In the Duplicate Values dialog box, make sure ‘Duplicate’ is selected.

How use VLOOKUP step by step?

Step 1: Organize the data. …
Step 2: Tell the function what to lookup. …
Step 3: Tell the function where to look. …
Step 4: Tell Excel what column to output the data from. …
Step 5: Exact or approximate match.

How do I compare two lists in Excel?

Select cells in both lists (select first list, then hold CTRL key and then select the second)
Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Press ok.
There is nothing do here. Go out and play!

Article first time published on

What is concatenate in Excel?

The word concatenate is just another way of saying “to combine” or “to join together”. The CONCATENATE function allows you to combine text from different cells into one cell. In our example, we can use it to combine the text in column A and column B to create a combined name in a new column.

How do I compare two columns in Excel using VLOOKUP?

Click the Compare two columns worksheet tab in the VLOOKUP Advanced Sample file. …
Add columns in your workbook so you have space for results. …
Type the first VLOOKUP formula in cell E2: …
Click Enter on your keyboard and drag the VLOOKUP formula down through cell C17.

What are the 5 parts of the VLOOKUP function?

The value you want to look up;
The range in which you want to find the value and the return value;
The number of the column within your defined range, that contains the return value;
0 or FALSE for an exact match with the value your are looking for; 1 or TRUE for an approximate match.

What is macro explain?

A macro is an automated input sequence that imitates keystrokes or mouse actions. A macro is typically used to replace a repetitive series of keyboard and mouse actions and used often in spreadsheets and word processing applications like MS Excel and MS Word. The file extension of a macro is commonly .

How do I create a macro?

Using the mouse that you want to configure, start Microsoft Mouse and Keyboard Center.
In the list under the button that you want to reassign, select Macro.
Click Create a new Macro. …
In the Name box, type the name of the new macro.
Click in Editor, and enter your macro.

How do you calculate Hlookup in Excel?

Summary. The Excel HLOOKUP function finds and retrieve a value from data in a horizontal table. …
Look up a value in a table arranged horizontally.
The matched value from a table.
=HLOOKUP (lookup_value, table_array, row_index, [range_lookup])
lookup_value – The value to look up.

Is index better than VLOOKUP?

With sorted data and an approximate match, INDEX-MATCH is about 30% faster than VLOOKUP. With sorted data and a fast technique to find an exact match, INDEX-MATCH is about 13% faster than VLOOKUP. Additionally, however, you can use a version of the INDEX-MATCH technique to calculate MUCH more quickly than with VLOOKUP.

Is VLOOKUP easy to learn?

While Vlookup is only one function in the world of spreadsheet management, its perhaps the most valuable and impactful one you can learn. By the way, you can also use its sister function, Hlookup, to search for values in Horizontal rows instead of Vertical columns. Take 5 minutes and learn Vlookup.

What column number is an?

Column LetterColumn NumberAN40AO41AP42AQ43

What is the formula for Find and Replace?

To replace text or numbers, press Ctrl+H, or go to Home > Find & Select > Replace. In the Find what box, type the text or numbers you want to find. In the Replace with box, enter the text or numbers you want to use to replace the search text. Click Replace or Replace All.

What is index on Excel?

Summary. The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers.

How do I match data in two Excel spreadsheets?

Step 1: Select your worksheets and ranges. In the list of open books, choose the sheets you are going to compare. …
Step 2: Specify the comparing mode. …
Step 3: Select the key columns (if there are any) …
Step 4: Choose your comparison options.

How do you separate names in Excel?

Add an empty column by right-clicking on the top of the column next to the existing column of names, then select Insert. …
Click the Data tab.
Click on the top of the column with your contacts’ names to highlight the whole column.
Click Text to Columns.
Select “Delimited” and click Next.

How do you CONCATENATE 3 columns in Excel?

Open your spreadsheet.
Select the cell where you want to display the combined data.
Type =CONCATENATE(AA, BB, CC) but insert your cell locations. …
Adjust the formula to include any needed spaces or punctuation.

What's the difference between concat and CONCATENATE?

The CONCAT function combines the text from multiple ranges and/or strings, but it doesn’t provide delimiter or IgnoreEmpty arguments. CONCAT replaces the CONCATENATE function. However, the CONCATENATE function will stay available for compatibility with earlier versions of Excel.

What is concatenation explain with an example?

In formal language theory and computer programming, string concatenation is the operation of joining character strings end-to-end. For example, the concatenation of “snow” and “ball” is “snowball”.

Can you do a VLOOKUP on 2 columns?

VLOOKUP doesn’t handle multiple columns. … You can find matches for Movie and Showtime columns individually but to find a match based on both the columns, you would need to modify the VLOOKUP formula.

What is Pivot in Excel?

A pivot table in Excel is an extraction or resumé of your original table with source data. A pivot table can provide quick answers to questions about your table that can otherwise only be answered by complicated formulas.

How does H look up work?

HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row.