Excel pull data from another sheet based on criteria

Excel pull data from another sheet based on criteria

Using several functions, Excel can simply retrieve data from another page depending on various criteria. We don’t have to type data for different sheets over and over. Today, we’ll learn about another application for Excel’s functions.

We have four main methods we can extract data from another sheet in excel. These methods are;

  • Use of VLOOKUP
  • Use of HLOOKUP
  • Use of Advanced filter
  • Use of combined index and match functions

Using VLOOKUP to extract data from another worksheet

VLOOKUP is an abbreviation for ‘Vertical Lookup.’ It is a function that causes Excel to look for a certain value in a column (the so-called ‘table array’) in order to retrieve a value from another column in the same row.

Let’s take a look at the datasets below and try to work on them. For great results follow the steps and procedures closely and carefully.

We can see the missing data which is ‘year’ is in another sheet 2 and needs to be exported to sheet 1.

Follow the steps to export the data into the spreadsheet 1

The syntax for vlookup is

[code]=VLOOKUP(lookup_value,table_array, col_index_number, [range_lookup]) [/code]

Here, Lookup_value is a unique identifier that serves as a reference to the data that needs to be exported. Table_array is the entire dataset or table. Col_index_number is the position of the column you wish to export from the dataset.

The optional argument at the end is (true/false). True is if we want an approximation of the values and false or 0 is if we want the exact figures.

Select cell D2 and type the formula

[code] =VLOOKUP(A2,DATA,2,0) [/code]

Press Enter

After that, drag down the fill handle down through the column

Now we can see the final results as displayed above

Using Hlookup to extract data from another worksheet

The HLOOKUP Function performs a horizontal lookup to retrieve the value from the data.

Let’s analyze the dataset below to see how to go about this

We are extracting the mode of examination into a different spreadsheet.

Follow the steps below to use HLOOKUP to do the extract

Select cell B5, input the formula

[code]=HLOOKUP($B$5,’HLOOKUP Function’!$B$4:$E$8,Sheet4!D5+1,0)[/code]

Press enter and drag the fill handle to the bottom of the column.

Using advanced feature to extract data from another sheet

The following steps will guide you to use the advanced feature to extract data

In this example, we are going to extract the student ID from the table to another sheet

  • Select data from the ribbon in the second sheet
  • Click on advance from the sort and filter group
techpady virtual classroom

Digital School Academy

Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials

  • Select copy to another location in the dialogue box
  • Select the range from the source sheet

Select the criteria range

Select the cell where you want to copy the extracted data

Finally, we see the extracted data of students who took part in the online mode of examination

Using combined index & match functions to extract data from another worksheet

The INDEX & MATCH Functions combo in Microsoft Excel is a popular and powerful tool for returning the value from a specific portion of the list. We can use this combination to retrieve data from another sheet based on criteria.

  • In another sheet below, we are going to extract the year of these students
  • Select cell B2
  • And input the formula below

[code]=INDEX(‘INDEX & MATCH Functions’!B5:E5,MATCH($B$5,’INDEX & MATCH Functions’!$B$4:$E$4,0))[/code]

  • Press enter
  • Your work is done

Post navigation

Leave a Comment

Leave a Reply

Your email address will not be published. Required fields are marked *