How to vlookup and return multiple values in Excel

-

From How to vlookup and return multiple values vertically in Excel? (extendoffice.com) 

How to vlookup and return multiple values (vertically) in Excel?

Normally, you can use the Vlookup function to get the first corresponding value, but, sometimes, you want to return all matching records based on a specific criterion. This article, I will talk about how to vlookup and return all matching values vertically, horizontally or into one single cell.

Vlookup and return all corresponding values vertically

Vlookup and return all corresponding values horizontally

Vlookup and return all corresponding values into one cell

Vlookup and return all corresponding values vertically

To return all matching values vertically based on a specific criterion, please apply the following array formula:

1. Enter or copy this formula into a blank cell where you want to output the result:

=IFERROR(INDEX($B$2:$B$15, SMALL(IF($D$2=$A$2:$A$15, ROW($A$2:$A$15)-ROW($A$2)+1), ROW(1:1))),"" )

Note: In the above formula, B2:B15 is the column contains the matching record you want to return; A2:A15 is the column contains the criterion; and D2 is the specific criterion that you want to return values based on. Please change them to your need.

2. Then, press Ctrl + Shift + Enter keys together to get the first value, and then drag the fill handle down to get all the corresponding records as you need, see screenshot:

Vlookup and return all corresponding values horizontally

If you want to get the matching values displayed in horizontal order, the below array formula can help you.

1. Enter or copy this formula into a blank cell where you want to output the result:

=IFERROR(INDEX($B$2:$B$15,SMALL(IF($E$1=$A$2:$A$15,ROW($A$2:$A$15)-ROW($A$2)+1),COLUMN(A1))),"")

Note: In the above formula, B2:B15 is the column contains the matching record you want to return; A2:A15 is the column contains the criterion; and D2 is the specific criterion that you want to return values based on. Please change them to your need.

2. Then, press Ctrl + Shift + Enter keys together to get the first value, and then drag the fill handle right to get all the corresponding records as you need, see screenshot:

Vlookup and return all corresponding values into one cell

To vlookup and return all corresponding values into a single cell, you should apply the following array formula.

1. Enter or copy below formula into a blank cell:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))

Note: In the above formula, B2:B15 is the column contains the matching record you want to return; A2:A15 is the column contains the criterion; and D2 is the specific criterion that you want to return values based on. Please change them to your need.

2. Then, press Ctrl + Shift + Enter keys together to get all matching values into a single cell, see screenshot:

Tips: This formula only applied successfully in Excel 2016 and later versions. If you don’t have Excel 2016, please view here to get it down.


Comments

Post a Comment

Popular posts from this blog

Add GitHub Repository to DBeaver CE Secured with SSH Protocol

-
Image
  From  Add GitHub Repository to DBeaver CE Secured with SSH Protocol - tech jogging Add GitHub Repository to DBeaver CE Secured with SSH Protocol Date  2021-06-14 Views 2579 Category DBeaver ,  Git GitHub is a good addition to DBeaver Community Edition (CE). It allows to store scripts and other files in source control. Even with some limitations in DBeaver CE, GitHub full functionality can be utilized. There are varieties of ways connecting GitHub to DBeaver CE. SSH protocol provides with enhanced security and seamlessly integrated with DBeaver CE. In some cases, it can work when HTTPS one might be problematic. The article uses DBeaver 21.1.0 CE installed in Windows. 1. Prerequisite. Create SSH key Follow  Create SSH Key in DBeaver CE  article. 2. Prerequisite. Install DBeaver Git extension Follow  Install DBeaver CE Git Extension  article. 3. Open File menu and select Import menu item 4. Expand Git node and select any item, for example, Projects from Git 5. Select Clone URI 6. Go to
Read more

Keyboard Shortcut to "Toggle Line Comments" in DBeaver

-
Image
In DBeaver, "Ctrl+/" is the default shortcut to Toggle Line Comments. However, in keyboards without a dedicated / key, this may not work out of the box. In my case, the / is obtained with "Shift+7", which means I'd need to set "Ctrl+Shit+7" as the shortcut,  which DBeaver uses by default as the shortcut to "Activate results/panel": So, to set enable the Toggle Line Comments, I needed to remove the "Activate results/panel" configuration from the Preferences and I'm now able to use "Ctrl+Shit+7" for the Toggle Line Comments.
Read more

DBeaver Shortcuts

-
From  DBeaver 6.0 Shortcuts - Cacher Snippet   CTRL + ENTER = Executes SQL. CTRL + ALT + ENTER = In Editor, inits statement templates ALT + X = Executes All queries in current editor CTRL + ALT + SHIFT + X = Execute a script opening each query results in a separate tab CTRL + ] = New SQL Editor Windows CTRL + SHIFT + F = Format SQL CTRL + 9 = Set active connection CTRL + 0 = Set active Schema CTRL + 6 = Toggle Results Panel CTRL + SHIFT + 6 = Maximize Results Panel Tab (when a datarow is selected) = Toggle vertical view CTRL + Alt + Right/Left (When in vertical view) = Browse rows CTRL + SPACE = To perform some object name auto-complete F4 = To open current object (under cursor) definition From  Shortcuts · dbeaver/dbeaver Wiki · GitHub Here is a complete list of default hotkeys in DBeaver UI for Windows, Linux, and macOS users. It will help you work in DBeaver faster and more efficiently. Remember that you can always change the keyboard shortcut in the DBeaver settings. Go through:  W
Read more