2 Easy steps to help you lookup data in Excel using multiple criteria

In last week’s tip, I showed you how to extract more than one column of data with a single Vlookup formula. However, if you want to lookup data with multiple criteria then you can use the INDEX and MATCH functions.

You can lookup data that meets multiple criteria and overcome the limitations of the Vlookup, thus saving you time and making your process more efficient.

Follow the example below, as I explain how to use the invoice number and order number to extract the customer’s name.

You are welcome to download the workbook to practice.

Applies To: Microsoft® Excel® 2010 and 2013.

1.Place the cursor in cell B4 and enter the following formula:

=INDEX(Data_Source,MATCH(B2&B3,Invoice_Number&Order_Number,0),4)

  • To display the data range for the entire table, place the cursor after “=INDEX( “.
    • Press F3 and select Data_Source.
  • B2&B3 represents the invoice number and order number respectively.
  • To display the data range for the invoice number, place the cursor after “MATCH(B2&B3,”.
    • Press F3 and select Invoice_Number.
  • To display the data range for the order number ,place the cursor after “MATCH(B2&B3,Invoice_Number&”
    • Press F3 and select Order_Number.
  • 0 means an exact match should be found.
  • 4 is the position of the customer name field within the main table range.

2.Press CTRL +SHIFT +ENTER because it is an array formula. Check the link below for more details on the array formula.

By using this formula you can lookup data with multiple criteria. It will let you analyze your data quicker and help save time! This will enable you to analyze data quickly.  For more details on the Index &Match function click on the link below.

 

NB: Depending on your computers region settings, if you encounter any problems with the attached tip formula, try and replace the , (comma) with a ; (semi-colon) where applicable.