How to Return Multiple Matching Rows and Columns Using VLOOKUP in Excel?

VLOOKUP function is a premade (already made by Ms-Excel) function in Excel by which we can search for any information in a given spreadsheet. We can use the VLOOKUP function in two ways, first is VLOOKUP with an exact match and VLOOKUP with an approximate match. VLOOKUP with exact match means that kind of data from which we can able to find same to same i.e; exact value from the given table. But VLOOKUP with the approximate match is something in which we can not able to find exact value from data But we can find approximate value, for eg., when we are finding the grade of students for their results then that grade comes from approximation means that grade lies between some value i.e; marks 50 – 60 lies under Grade B and if some student got 55 marks then they lie under grade B for that particular subject, So, here approximation occurs, which lies in VLOOKUP with an approximate match. Whenever we use any formula in excel the syntax is =FormulaName and by default, formulas are always in uppercase only, although we can write in lowercase also, for eg.  =VLOOKUP().

Returning Multiple Matching Rows and Columns Using VLOOKUP

VLOOKUP function has four parameters VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) . lookup_value means the value based upon which you want to perform a search. table_array means the entire table (including headers of that table also) where you perform searching. col_index_num means, the serial number of that column which you want to search. and the last and fourth parameter is [range_lookup], it has simply two values, either 0 or 1. for the exact VLOOKUP match we use 0 and for the approximate VLOOKUP match we will use 1. 

There is a condition for the VLOOKUP function that the lookup_value should always be in the first column of that table, then only we can use the VLOOKUP function. For Return Multiple Rows and Columns using the VLOOKUP function in Excel, we will follow some steps which are mentioned below:

Step 1: Let’s take any set of data from which you want to return multiple matching rows and columns. For eg., here, we are taking the Orders data of MNCs. 

Step 2: Now, what we are going to do is that we have to “return the multiple rows and columns which are matching to Amazon” using the VLOOKUP function.

Step 3: First we need to find the occurrence of each company name that how many times they appear in the data. For doing this we need to insert a new column (for inserting a column just click on Company cell -> right click -> choose “insert”). Apply the formula “=B4&COUNTIF($B$4:B4,B4)”. You need to write this formula according to your own Excel cells value. Here, B4 is the cell’s name of our target Company name, and need to lock that particular cell in both ways (row-wise and column-wise). for locking the cells you need to press the fn + f4 key 2 times, $ sign indicates the lock in excel, $A means the Ath column of excel is locked and $2 means the 2nd row is locked. Then we find the 1st occurrence of our targeted company name, written as “Amazon1”.

Step 4: When you scroll down or drag the 1st occurrence result till the last row of that data then Similarly excel find the occurrence for selected/rest of the company’s name in that data.

Step 5: Now, use the VLOOKUP function and put all four parameter values, which we already discussed above (lookup_value, table_array, col_index_num, [range_lookup]). Lookup_value is your targeted company name (Amazon). you need to select that name cell and lock them. that is $G$3&Row()-3, here we subtract -3 because the current location is in the 4th row so to make it in the 1st row we need to subtract -3 from them. For table_array just select the whole table including headers. 

For Column index numbers match(J$3,$A$3:$D$3,0), here the match function returns the position of a lookup item .in match function itself three-parameter, first is lookup_value, lookup_array, [match_type]. $ represents the locking of that rows/columns. Our fourth parameter [range_lookup] is 0 for an exact match.

At final our formula became =IFERROR(VLOOKUP($G$3&ROW()-3,$A$3:$D$18,MATCH(J$3,$A$3:$D$3,0),0),””), here we used iferror formula to prevent the #N/A .

Step 6: Drag it to the bottom of the table and then finally it will return the multiple matching rows and columns using the VLOOKUP function in Excel.