3 Ways to Filter Images in Excel

Filter Image in Excel

Vlookup, Hlookup, Index, and match functions are used to search data and retrieve the desired results based on the given conditions. The alphabetical, string, and numerical data can be easily obtained using these functions. Nevertheless, it can be challenging to obtain the associated image from the Employee database or table, if you wish to do so using the Employee code or name. However, in the latest version of Excel, retrieving an image from a database that based on some criteria is quite simple using formula too.

So, we are trying to tell you how we can retrieve pictures as per given condition or query from a table or database in Excel using three different methods.

Maintain Images within the Cell

First of all maintain position of your images in cell properly, the images should be inside the cell only. If you are using Excel365 then there is an option called “Place in Cell” but in earlier version you have to select picture then right click to choose size & properties, again properties and then select “Don’t Move or Size  with cells. This option keeps the images intact in the cell only.

There are three ways to retrieve the images from a database or data table with the help of excel function.

Method-1  Using Index() & Match() function

First way is Index () and match () function along with named feature of MS-Excel to retrieve the images from a database or data table dynamically.

Method-1 First Step 

In this first step, we create a dropdown list of data to be used as a condition. Like if you are retrieve image on the basis of name then create a Dropdown List for Names.

Data Validation-1

• Move the pointer to the desired location to see the Dropdown List.

• Go to Data Menu

• Under setting (1) Option allow validation criteria.  

• Click on “Any Value” (2) & select list.

• Click on “Source” (3)

Data Validation-2

Now, your dropdown list is ready.

You can create dropdown list using named range feature also.

Press Ctrl +F3 shortcut, then select the name for dropdown list. 

Method-1 Second Step

In this step create a named range using index and match function. This named range is dependent on the drop-down and when we change the selection in the drop-down, it returns the reference of the cell next to the selected one.

INDEX () FUNCTION

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

Syntax

INDEX(array, row_num, [column_num])

MATCH () FUNCTION

Use MATCH when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.

Syntax

MATCH(lookup_value, lookup_array, [match_type])

Method-1

Example 

Define Name Menu 

In the above example a table is defined through range B2:C7.

Select the range and press “Ctrl +T “to convert the range into table. Now, use shortcut Ctrl +F3 or click formula and select Define name from Defined Names (as shown in the figure below)

In the above window we have create a named range LOGO refers to Index (sheet1!$C$4:$C$7, MATCH(Sheet1!$B$11,Sheet!$B$4:$B$7,0)

Define Menu Dialog Box 

Method-1 Third Step

In third step create a linked picture from your database or table. When we create a linked picture, it refers to the original cell from which it was copied. We change that reference with the named range after connecting.

Method-1 Fourth Step

In the last or fourth step just linked the picture with the whole data.

• Select the image

• Click on Formula Bar

• Type = (named_range) i.e. =LOGO   

Method-2 Using Named Ranges & Indirect() Function

This method use indirect function and named ranges to retrieve the image based on conditions. It is a five step procedure to retrieve the image from a database or table.

Method-2

Method-2: Step-1

Define Name Menu 

This step is to create a dropdown list of data to be used as a condition. Just create a named range for the list (like =LIST)

Just by pressing shortcut key Ctrl+ F3, Select New, Define name and ranges.

Now go to Data >> Data Validation

Now follow all the steps of Method-1: Step-1

Data Validation-2

Besides in source data instead of mentioning range input Named Range only.

Method-2: Step-2 (Name from Selection)

Create Name from Selection

In second step select the cell, range of cells, or nonadjacent selections that you want to name. Press Ctrl + Shift + F3. This creates names by using those of either row or column labels. Type the name you want to use to refer to your selection.

This way we refer both the cell with same reference content.

Method-2: Step-3 

In third step create a linked picture from your database or table. When we create a linked picture, it refers to the original cell from which it was copied. 

Method-2: Step-4

In this step create a named range for dropdown selection using Indirect () function. 

Syntax 

INDIRECT(ref_text, [a1])

It means a reference to a cell that contains an A1-style reference

The INDIRECT function helps us put the address of one cell (A1 in our example) into another as a usual text string, and then get the value of the first cell by acknowledging the second.

Method-2: Step-5

This is the final step to connect the picture with indirect () named range. This way when anyone selects the dropdown name randomly, then the connected image will be displayed in the connected cell.

Method-3 Using Vlookup() & Filter() Function

Third method is the easiest one and it is basically used in higher version of Excel like Excel 365. There is no need of lot of function and tricks to retrieve the image of a person or item from a database or query. It is a simple approach to filter the images.

Method-3

First of all define a table and place the images in the cell (There is a feature in Excel365 to insert the image in a cell.)

Vlookup() Function : 

Vlookup() function can return images that are stored within cells, just like any other type of returnable data.

Syntax

=vlookup (lookup_value, Table_array, col_index_num, range lookup) 

=vlookup (lookup_value, range of table, Column no., False) 

Thereafter, first choose the cell for lookup value, second define the range of table, third select the column no. and in the last choose false option for exact match. This way you can easily retrieve the images from table.

Filter() Function :

The Excel FILTER function is used to extract matching values from data based on one or more conditions. Filter () function is used to return multiple images in Excel365.

=FILTER(array,include,[if_empty])

• array - Range or array to filter.
• include - Boolean array, supplied as criteria.
• if_empty - [optional] Value to return when no results are returned.

These functions available in Excel365 and it is quite easy and useful to use these functions. But if the latest version not available to you, don't worry you can use Method-1 & 2 as per your ease and understanding.

Watch YouTube Video also: 

*********