To demonstrate the different methods, we will use a dataset of sales information, consisting of the sales of particular products by different sales reps in different regions.

### Method 1 – Use VLOOKUP Function

__Steps:__

- In column
**E**, insert a column in the dataset named “Helper”. - In cell
**E5**enter the following formula

`=B5&C5&D5`

- Press
**ENTER**to return the output.

- Use
**Fill Handle**to**AutoFill**up to cell**E14**.

Now let’s compare the three columns *Sales Rep**, Region, and Product *with the same columns from another table, and retrieve the *Sales* for matches.

__Steps:__

- In cell
**K5**enter the following formula:

`=VLOOKUP(H5&I5&J5,$E$5:$F$14,2,0)`

- Press
**ENTER**to return the output.

__Formula Explanation__

- Here, the
**lookup_value**is**H5&I5&J5**. - The
**table_array**is**$E$5:$F$14**. Excel will look for**H5&I5&J5**in this array. - The
**col_index**is**2**. Excel will return the corresponding*Sales*.

- Use
**Fill Handle**to**AutoFill**up to cell**K14**.

### Method 2 – Combine INDEX and MATCH Functions

__Steps:__

- In cell
**J17**enter the following formula:

`=INDEX($E$5:$E$14,MATCH(1,($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5),0))`

- Press
**ENTER**to return the output.

__Formula Breakdown__

**($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5)**→ This is the**lookup_array**for**the MATCH function**.**Output:**{1;0;0;0;0;0;0;0;0;0}

**MATCH(1,($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5),0)****→**This is the**row_num**for**the INDEX function**.**Output:**1

**INDEX($E$5:$E$14,MATCH(1,($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5),0))**- This formula resolves to
**INDEX($E$5:$E$14,1)****Output:**$16,800

- Use
**Fill Handle**to**AutoFill**the formula up to cell**J14**.

### Method 3 – Use the IF Function

__Steps:__

- In cell
**J5**enter the following formula:

`=IF(B5:B14=G5:G14,IF(C5:C14=H5:H14,IF(D5:D14=I5:I14,E5:E14)))`

- Press
**CTRL + SHIFT + ENTER**to return the output. Since this is an array formula, all sales will be returned at once.

__Formula Explanation__

- This is a
**nested IF formula**. **B5:B14=G5:G14**is the logic test for the first**IF**function.**Output:**{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}

- Since the output is
**TRUE**, the result will be**IF(C5:C14=H5:H14,IF(D5:D14=I5:I14,E5:E14))** - The rest of the
**IF**functions work similarly to the first one.**Output:**{16800;18700;45780;9040;10100;24500;50000;5500;20000;2000}

Note the curly bracket **{}** in the formula bar, which indicates an array formula.

### Method 4 – Apply SUMPRODUCT Function

__Steps:__

- In cell
**J5**enter the following formula:

`=SUMPRODUCT(--(B5:B14=G5),--(C5:C14=H5),--(D5:D14=I5),E5:E14)`

- Press
**ENTER**to return the output.

__Formula Explanation__

- There are four arrays in the formula.
- The output for the first three arrays is the same:
**{1;0;0;0;0;0;0;0;0;0}** - Hence the sum products of these arrays are the corresponding
*Sales*.

- Use
**Fill Handle**to**AutoFill**the formula up to cell**J14**.

**Download Practice Workbook**

**<< Go Back to Columns | Compare | Learn Excel**

Hello, I have 3 columns. employee ID, date and dollars. Not all dates for each employee have dollar. I want to return dates that have dollars for each employee. How do I proceed.

Thank you

Hello Jim,

Thanks for commenting. If I am not wrong, you want to sort dates that don’t have the dollar like the dataset below.

Select the entire data range then navigate the

Hometab >> chooseSort & Filterfrom #diting group >> pickFilter.Choose the Filter dropdown and uncheck the blanks from the column.

Finally, the dates are sorted with the dollar.

Regards

Fahim Shahriyar Dipto

Excel & VBA Content Developer

Hello, i want to match 3 columns Customer name, ID name and Real name resp. and if both name not matched with ID name then , they should be highlighted with colour.

ex.

jenith jeneth jenath riq

Hello

Chandan,Thanks for commenting. If I’m not wrong, you want to match the dataset like the one below.

Select the entire dataset without the heading then go to conditional formatting >> New Rule

Then select “Use a formula to determine which cells to format.”

In the formula bar, enter the following formula:

=AND(A3<>B3,C3<>B3)Press OK

Here, “A3” represents the cell containing the customer name, “B3” represents the cell containing the ID name, and “C3” represents the cell containing the real name. You can adjust the cell references based on your data.

Choose the formatting option that you want to use for the highlighted rows. For example, we took light green color here for highlighting.

The final outcome will look like the image below.

I hope this answer will help you to identify matched names. Please let us know if you have any other queries. Also, you can post your Excel-related problem in ExcelDemy Forum with images or Excel workbooks.

Regards

Mizbahul Abedin | ExcelDemy Team