Excel Tip: Creating a lookup formula in Excel with multiple criteria

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

How would you accomplish a table search that will match a product with a sample list, compare the price with the various samples, and then pick the results from another column based on the comparison conditions? Vlookup function cannot perform such tasks.

How to perform a complex lookup with multiple conditions in Excel

Here is a sample problem.

Suppose you have a table as shown in the image below:

The sample lookup table

Table 2 above shows a list of products and their price ranges. There are three (3) price ranges for each product and there can be more. The whole problem is filling up the Markup column in Table 1 below:

Sample problem filling up the markup column

This is how thew Markup column above must be computed:

  1. Excel should search the product name in Table 1 with that in Table 2. (Eg. Apples)
  2. When found, Excel should compare price of the product in Table 1 with the prices in Table 2 and ensure it is WITHIN the MIN and MAX value.
  3. When the MIN and MAX value is determined, Excel should then copy the Markup value from Table 2 to Table 1. See the actual sample file attached.

The Solution

In order to solve the problem, we need to use an array formula that uses the combination of INDEX and MATCH functions. For the sample Excel file attached, the working formula is this:

{=INDEX($K$4:$K$18,MATCH(1,(C4=$G$4:$G$18)*(D4>=$I$4:$I$18)*(D4<=$J$4:$J$18),0))}

Please note that the curly braces are not entered manually. They indicate that the formula is an array and it is entered automatically by Excel when you press CTRL+SHIFT+ENTER.

The INDEX formula will return the Markup values in column K based on three (3) conditions. First, the value of C (product name in Table 1) must be equal to the product names in column G (product names in Table 2. Second, the price of the product in Table 1 (column D) must be greater than or equal to the price list in column I in Table 2. This is to determine the MIN value. Lastly, third is the price must be less than or equal to the price list in column J, thus the MAX value.

When a match is found, then the Markup value in column K will then be returned. See the attached demo file.

MS Office Software: 
AttachmentSize
File Sample_Data_TargetMarkup.xlsx15.87 KB