In this first post, we\u2019ll see how the FILTER function can return multiple column values. This is much different from VLOOKUP which is designed to return a value from a single column.<\/p>\n\n\n\n
Return Multiple Column Values<\/h2>\n\n\n\n
Before we get too far, let\u2019s just confirm the Excel scenario we are trying to tackle.<\/p>\n\n\n\n
We have some transactions, like this:<\/p>\n\n\n\n We need to retrieve the account names and groups from a lookup table like this:<\/p>\n\n\n\n One option would be to use a traditional lookup function such as VLOOKUP. In this case, we would write two formulas, one to retrieve the Name and another to retrieve the Group. And that would be fine. In fact, this is a common method we\u2019ve used for decades. But, now we have another option. The FILTER function can return multiple columns. This means that we can write one formula instead of two (or more) to retrieve multiple column values.<\/p>\n\n\n\n Note: depending on your version of Excel, you may not have access to the FILTER function. At the time of this writing, it is available in Excel 365.<\/em><\/p>\n\n\n\n The FILTER function is designed to return a subset of data, meaning, selected rows (or columns) from a data table. The basic syntax is this:<\/p>\n\n\n\n Where:<\/strong><\/p>\n\n\n\n So, Jeff \u2026 that doesn\u2019t really sound like a lookup function. I know. And so often, when an Excel user is presented with a lookup task, traditional lookup functions such as VLOOKUP immediately come to mind. But, sometimes, there are alternatives to VLOOKUP which provide some benefits.<\/p>\n\n\n\n Note: other examples include\u00a0SUMIFS\u00a0and\u00a0Power Query.<\/em><\/p>\n\n\n\n So, let\u2019s see how we can apply the FILTER function to accomplish our lookup task.<\/p>\n\n\n\n We want to write a formula in E7 <\/strong>to populate both the Name and Group values.<\/p>\n\n\n\n That formula should retrieve values from C15:D23<\/strong> by finding a matching AcctID in B15:B23<\/strong> here:<\/p>\n\n\n\n So, in cell E7<\/strong>, we write the following formula:<\/p>\n\n\n\n We hit Enter \u2026 and bam:<\/p>\n\n\n\n That one formula returns both the Name and Group values \u2026 yay!<\/p>\n\n\n\n We can fill the formula in E7 down to populate the remaining transactions:<\/p>\n\n\n\n In this illustration, the column order was the same \u2026 Name <\/strong>and then Group<\/strong>. But \u2026 what if the column orders are different? No problem.<\/p>\n\n\n\n Let\u2019s say the column order is different. For example, Group then Name in one place:<\/p>\n\n\n\n And Name then Group in another:<\/p>\n\n\n\n One way to accomplish this is to FILTER a FILTER. In other words, we use FILTER to retrieve all columns that match the AcctID like we did previously with this formula:<\/p>\n\n\n\n And then FILTER those results to only include one column. We can tell the FILTER function which column we want based on matching the column labels.<\/p>\n\n\n\n We wrap another FILTER function around the previous FILTER function and use the include <\/em>argument to compare the column labels in C14:D14<\/strong> with the column label in E6<\/strong>. We are careful to use the proper absolute\/relative references so that when we fill down\/right it continues to work as expected:<\/p>\n\n\n\n We hit Enter and bam \u2026<\/p>\n\n\n\n Now, we can fill that formula down and right \u2026 and bam:<\/p>\n\n\n\n Yay \u2026 we did it!<\/p>\n\n\n\n As you can see, the FILTER function provides an alternative to traditional lookup functions such as VLOOKUP. One benefit is that it supports multiple\u00a0return\u00a0<\/em>columns \u2026 which is another way to say it can return multiple values from related columns.<\/p>\n\n\n\n Ref: https:\/\/www.excel-university.com\/perform-lookups-with-filter-1<\/p>\n","protected":false},"excerpt":{"rendered":" This is the first post in a series where we\u2019ll talk about how to use the FILTER function as an alternative to lookup functions including VLOOKUP. Why? Well, as we\u2019ll discover in this series, the FILTER function offers several key benefits. For example, FILTER supports: Multiple return columns Multiple lookup columns Multiple matching row values In this first post, we\u2019ll see Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[952,951],"class_list":["post-3785","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-lookups-with-returning-multiple-column-values","tag-multiple-column-values"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923a.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923b.png\")
<\/figure>\n\n\n\nDetails<\/h2>\n\n\n\n
=FILTER(array, include, [if_empty])<\/pre>\n\n\n\n
Illustration<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923c.png\")
<\/figure>\n\n\n\n<\/figure>\n\n\n\n=FILTER($C$15:$D$23,$B$15:$B$23=D7)<\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923d.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923e.png\")
<\/figure>\n\n\n\nWhat if the column order is different?<\/h3>\n\n\n\n
![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923f.png\")
<\/figure>\n\n\n\n<\/figure>\n\n\n\n=FILTER($C$15:$D$23,$B$15:$B$23=D7)<\/pre>\n\n\n\n
=FILTER(FILTER($C$15:$D$23,$B$15:$B$23=$D7),$C$14:$D$14=E$6)<\/pre>\n\n\n\n
![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923g.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.excel-university.com\/wp-content\/uploads\/20200923h.png\")
<\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n