How to vlookup multiple values in Excel

How to vlookup multiple values in Excel

Vlookup is a regular excel operation for identifying single values that correspond to lay down criteria. This does not imply that it is impossible to look up for multiple values. Excel is a hub of possibilities, so follow me, let me walk you through the procedure of looking up multiple values. You can vlookup and display matching values into a single cell, horizontally or vertically.

 

 

 

 

 

 

 

Vlookup multiple values vertically.

You can use a carefully crafted array formula to vlookup multiple values that meet up certain criteria vertically.

Here is the array formula:

=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))),”” )

1. The reference column is B2: B15, where all the matching records are embedded.

2. All the criteria are in A2: A15, while the specific criterion that you wish to vlookup is contained in D2. This is only but an example. You are free to change the values to suit your work.

 

 

 

 

 

 

3. Identify a suitable blank cell where you want to display your result. Copy the formula and paste in the cell.

4. Deduce the first value by pressing together ”Ctrl + Shift + Enter” keys. Click the fill handle and drag down to get vlookup multiple values as you desire.

Vlookup multiple values horizontally.

You can follow the procedure in this section if you want to display the results of your vlookup horizontally.

Here is the array formula:

=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))),””)

1. The reference column is B2: B15, where all the matching records are embedded.

2. All the criteria are in A2: A15, while the specific criterion that you wish to vlookup is contained in D2. This is only but an example. You are free to change the values to suit your work.

 

 

 

 

 

 

 

3. Identify a suitable blank cell where you want to display your result. Copy the formula and paste in the cell.

4. Deduce the first value by pressing together ”Ctrl + Shift + Enter” keys. Click the fill handle and drag down to get vlookup multiple values as you desire.

Vlookup multiple values in one cell

You can follow the procedure in this section if you want to display the results of your vlookup in a single cell.

Here is the array formula:

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

1. The reference column is B2: B15, where all the matching records are embedded.

2. All the criteria are in A2: A15, while the specific criterion that you wish to vlookup is contained in D2. This is only but an example. You are free to change the values to suit your work.

Identify a suitable blank cell where you want to display your result. Copy the formula and paste in the cell.

 

 

 

 

 

 

Deduce the first value by pressing together ”Ctrl + Shift + Enter” keys. Click the fill handle and drag down to vlookup multiple values as you desire.