Combine VLookup

माइक्रोसॉफ्ट एक्सेल में 30 कॉलम में एक साथ VLOOKUP FORMULA कैसे लगायें। 

How to VLookup multiple columns in Excel – example

Here’s a process flow for using the combined VLOOKUP function:

Step 1: Prepare Data

1. Select the entire column range
   • Highlight the columns where you want to apply the combined VLOOKUP.

Step 2: Apply VLOOKUP Formula

1. Insert the VLOOKUP formula

   • In the formula bar, type the VLOOKUP formula:
     =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

2. Select the unique ID

   • For the lookup_value, select the unique ID from your data (this is typically the reference point you want to search).

3. Go to the data sheet

   • Navigate to the sheet where your lookup table or data range is located.

4. Select the data range

   • Select the data range starting from the unique ID column to the end of the data range (including all columns containing the information you want to retrieve).

Step 3: Apply the Formula

1. Remember the column number

   • The VLOOKUP formula will return a column number. For example, if the range is from 2C to 10C within curly brackets, [ Example: =VLOOKUP(B3,'Data sheet'!B:L,{2,3,4,5,6,7,8,9,10,11},0)]

2. Hold CTRL + SHIFT keys and press ENTER

   • After entering the formula, hold down the Ctrl + Shift keys and then press Enter to apply the formula to multiple rows at once.

Here is the VLOOKUP formula we have:

Here’s a step-by-step process for Method 2 to use a combined VLOOKUP:

---

Step 1: Select the Data Range

1. Select the entire column range
   • Highlight the columns where you want to apply the combined VLOOKUP.

---

Step 2: Apply the VLOOKUP Formula

1. Start typing the formula

   • In the formula bar, type the VLOOKUP formula:
     =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

2. Select the unique ID

   • For the lookup_value, select the unique ID in your data (the reference point for the lookup).

3. Go to the data sheet

   • Navigate to the data sheet where the lookup table is located.

4. Select the data range

   • Select the data range starting from the unique ID column to the last column containing the data you want to retrieve.

5. Insert a comma (,)

   • After selecting the data range, type a comma , to separate the next part of the formula.

---

Step 3: Use the COLUMNS Formula

1. Add the COLUMNS function

   • Use the COLUMNS function to calculate the column index dynamically:
     COLUMNS(data_range).
     • For this, select the range in the data sheet starting from the unique ID to the column where you want to begin retrieving data.
       Example: COLUMNS(A1:D1).

2. Close the formula

   • Ensure the formula is complete with the appropriate parameters.

---

Step 4: Finalize the Formula

1. Hold the CTRL function

   • After completing the formula, hold down the CTRL key (or CTRL + SHIFT, depending on your Excel version).

2. Press ENTER

• Press Enter while holding CTRL to execute the formula for combined VLOOKUP.

Sample applied Formula: 

=VLOOKUP($B3,'Data sheet'!$B:L,COLUMNS('Data sheet'!$B2:C2),0)

*Kindly Note: Freeze the all columns of unique ID by using dollar symbol before enter.

Click on below link to download excel file format:

}

Thank you