(iii) Similarly, for the third instance, the lookup column is Column B and the result column is Column L (i.e., 03-Jul-19). The difference between Column B and I is 8. (ii) Similarly, for the second instance, the lookup column is Column B and the result column is Column I (i.e., 30-Jun-19). The number of columns between Column B and D is 3. Whereas, the result column ( retrieve column) is column D which contains the sales values on 2. (i) For the first instance, the lookup column is Column B i.e., the column with Sales Manager. Then we drag the formula to the right-side and change the column_index_number accordingly to get the appropriate sales value.Īs the column_index_number plays an important role in Multiple Columns VLOOKUP, so we count the columns manually between the lookup column and the result column. Note: the upper or lower case does not matter in syntax, Excel by default considers it in upper case.Īt first, we want to retrieve the sales value against Sales Manager Mathew on dated 20-May-19. VLOOKUP syntax appears with an open parenthesis. Place an equality sign ( =) in cell P4 and type ‘ VLO…’, select VLOOKUP from the below suggestion list of Down Arrow key (⬇ ), and press the ‘ Tab’ key. We retrieve data from this master database with the VLOOKUP formula. ➢ Step1: For a basic understanding of Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel, we go through the Simple Method at the beginning where the column number needs to change manually.Ĭells range A3: L21 is the main database or master database. (01) Simple Method: Needs to Change the column_index_number Manually (04) Matrix Method: Using the INDEX, MATCH and MATCH nested functions, making the dynamic formula. (03) Matrix Method: Using the VLOOKUP and MATCH nested functions, making the dynamic formula. (02) Advanced Method: Using the predefined Column number(s) and Cell References, making the dynamic formula.
(01) Simple Method: Needs to change the column_index_number manually. Here we discuss 4 methodologies of Multiple Columns VLOOKUP or Multiple VLOOKUP in Excel: ➢ Then copy ( Ctrl+C) the cell P4 with formula and extends it to the right-side and down-side till the end of the range i.e., P4:R11. ➢ Using cell references ( relative, absolute, and mixed according to the requirement). ➢ Press Enter to apply the formula in cell P4. ➢ Assign the VLOOKUP formula or the INDEX formula.
➢ Select cell P4 by clicking on it or press F2 key or via the formula bar. In the following example, based on the list in cells A3:L21, to find out the sales value in cell P4 against the Sales Manager name mentioned in cell O4 and Sales Date mentioned in cell P3. Methods of Multiple Columns VLOOKUP in ExcelĪ single VLOOKUP formula with certain parameters is used to retrieve values/data from a table/dataset. Otherwise, the VLOOKUP formula returns an error. It makes a big sense to make a dynamic VLOOKUP formula. ➢ Thirdly, proper use of cell reference (relative, absolute, mixed) within first three VLOOKUP arguments (i.e., lookup_value, table_array, col_index_num). ➢ Secondly, we try out to make the column number or column_index_num to be dynamic. More clearly, the area between the lookup column and the last column of the active database.Īs a result, the selection range should not be expanded or diminished every time, need to change column number or column_index_num only. ➢ Firstly, we select the entire database area (range) means entire table_area or table_array. Basic Criteria of Multiple Columns VLOOKUP in Excel
(01) Simple Method: Needs to Change the column_index_number Manually.Methods of Multiple Columns VLOOKUP in Excel