Nov 29, 2014  Lookup data using multiple conditions - Duration: 7:31. Computergaga 236,660 views. Mix Play all Mix - ExcelVbaIsFun YouTube; Excel VBA FIND Function (& how to handle if value NOT found).

How to do an Excel lookup with multiple criteria in columns, with the INDEX and MATCH functions. Get the sample file, then watch the step-by-step video, or follow the written instructions.

Excel Lookup With Two Criteria

This video shows how the INDEX and MATCH functions work together, with one criterion. Then, the formula is changed, to work with multiple criteria.

To follow along with the video, download the sample file. There are written instructions too, in the next section.

• 0:00 Introduction
• 0:26 Lookup with One Criterion
• 1:52 Test Each Criterion
• 2:22 Test With a Formula
• 3:26 Multiply the Results
• 4:03 INDEX / MATCH Formula
• 5:20 Check the Formula
• 5:57 Get the Sample File

Lookup Functions

There are many ways to do a simple lookup in Excel, using functions such as VLOOKUP or HLOOKUP. In this example, we need to do a complex lookup:

• there are multiple criteria, instead of just one
• we need to find a product code, which is to the left of the criteria

VLOOKUP won't work here, so we'll use the INDEX and MATCH functions together, to get the results that we need.

INDEX and MATCH

To do this complex lookup with multiple criteria, we'll use the INDEX and MATCH functions.

• The INDEX function can return a value from a specific place in a list
• The MATCH function can find the location of an item in a list.

When INDEX and MATCH are used together, they create a flexible and powerful lookup formula. ▲TOP

Simple INDEX and MATCH

Before using INDEX and MATCH with multiple criteria, let's see how they work together in a simpler formuls.

In this lookup formula, we need to find “Sweater” in a column B of a price list, and get its price from column C.

• The item name that we need a price for is entered in cell A7 – Sweater.
• This INDEX and MATCH formula is entered in cell C7, to get the price for that item:

=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))

How INDEX MATCH Formula Works

Here’s how that simple INDEX / MATCH formula finds the sweater price:

• the MATCH function can find “Sweater” in the range B2:B4. The result is 1, because “Sweater” is in the first row of that range.
• the INDEX function can tell you that in the range C2:C4, the first row contains the value 10.

So, by combining INDEX and MATCH, you can find the row with “Sweater” and return the price from that row. ▲TOP

Match for Multiple Criteria

In the previous example, the match was based on one criterion -- the Item name. For the next lookup, there are 2 criteria -- Item name and product Code.

In the screen shot below, each item is listed 3 times in the pricing lookup table. To get the right price, you’ll need to specify both the item name and the size. We want to find the price for a large jacket.

MATCH True or False

In the lookup formula, we need the MATCH function to check both the Item and Size columns.

To show how that will work, I'll add temporary columns on the worksheet, to check the item and size columns -- is the item a Jacket, and is the Size a Large?

Enter this formula in F2, and copy down to F10: =C2=$C$13

• If the Item in column C is a Jacket, the result in column E is TRUE. If not, the result is FALSE

Enter this formula in G2, and copy down to G10: =D2=$D$13

• If the Size in column D iis Large, the result in column F is TRUE. If not, the result is FALSE

MATCH Both True

We need the price from the row where both results are TRUE. We'll use a formula to calculate that for us:

Enter this formula in H2, and copy down to H10: =F2*G2

In Excel, TRUE is equal to 1, and FALSE is equal to zero. When you multiply the values,

• If either value is FALSE (0), the result is zero
• If both values are TRUE (1), the result is 1

Only the 8th row in our list of items has a 1, because both values are TRUE in that row.

Lookup With Multiple Criteria

We could use a MATCH formula to find the position of a 1 in column G, in the screen shot above. The 8th row of data (worksheet row 9), has the 1, and that row will give us the correct price for a large jacket.

But, instead of adding extra columns to the worksheet, we will use an array-entered INDEX and MATCH formula to do all the work.

Here is the array-entered* formula that we’ll use in cell E13, to get the correct price:

• =INDEX(E2:E10,
  MATCH(1,
  (C13=C2:C10)*(D13=D2:D10),0))

*Press Ctrl + Shift + Enter, instead of just pressing the Enter key. That will automatically add curly brackets around the formula.

How the Complex Formula Works

Here's how this INDEX MATCH multiple criteria formula works. In this INDEX and MATCH example,

• prices are in cells E2:E10, and the INDEX function will return a value from that range.
• item name criterion is in cell C13
• size criterion is in cell D13.

The formula checks for the selected item name in $C$2:$C$10, and the selected size in $D$2:$D$10. The TRUE and FALSE results are multiplied, to return zeros and ones.

• (C13=$C$2:$C$10)*(D13=$D$2:$D$10)

To see how that works, select this part of the formula in the formula bar

Then, press the F9 key, to see the calculated results for the selected potion of the formula. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is

Next, select the MATCH function portion of the formula in the formula bar

Press F9, to see the result. The MATCH function looks for the 1 in the array of results, so in our formula, it returns 8.

Then, press the ESC key, to exit the function without saving the calculations.

As the final step in the formula, the INDEX function returns the price from the 8th data row in column E (cell E9). ▲TOP

Download the Sample File

To follow along with this tutorial, download the zipped sample Lookup with Multiple Criteria file. The workbook is in xlsx format, and does not contain any macros. ▲TOP

More Tutorials

30 Functions in 30 Days

-->

Locates and returns a Microsoft Outlook item object that satisfies the given Filter.

As for a distributor in India, we donot currently have one. Download varahamihira astrology software free.

Syntax

expression.Find (Filter)

expression An expression that returns a Items object.

Storyline: jay Rathoreis a no-nonsense police officer from Mumbai Crime Branch. Sarfarosh movie hd video download. He is a great admirer ofPakistan-based ghazal singer Gulfam Hassan. Long time agohis elder brother was killed by terrorists which caused him to quit hismedical studies and join Indian Police Service. During one of Hassan’sconcerts, they meet for the first time and become great friends.Meanwhile, Ajay’s investigation into the Pakistan-supported cross-borderterrorism takes him to Rajasthan and reveals an unsettling truth aboutsomeone close to his heart.

Parameters

Name	Required/Optional	Data type	Description
Filter	Required	String	A string that specifies the criteria that the returned object must satisfy.

Return value

An Object value that represents an Outlook item if the call succeeds; returns Null (or Nothing in Visual Basic) if it fails.

Remarks

To use content indexing search in the Items collection, use the Restrict method. FindRow will return an error if Filter contains content indexing keywords. For more information on content indexing keywords, see Filtering Items Using Query Keywords.

The method will return an error with the following properties in the Filter :

Body	LastFirstNoSpaceCompany
Children	LastFirstSpaceOnlyCompany
Class	LastFirstNoSpaceAndSuffix
Companies	MemberCount
CompanyLastFirstNoSpace	NetMeetingAlias
CompanyLastFirstSpaceOnly	NetMeetingAutoStart
ContactNames	NetMeetingOrganizerAlias
Contacts	NetMeetingServer
ConversationIndex	NetMeetingType
DLName	RecurrenceState
Email1EntryID	ReplyRecipients
Email2EntryID	ReceivedByEntryID
Email3EntryID	ReceivedOnBehalfOfEntryID
EntryID	ResponseState
HTMLBody	Saved
IsOnlineMeeting	Sent
LastFirstAndSuffix	Submitted
LastFirstNoSpace	VotingOptions
AutoResolvedWinner	DownloadState
BodyFormat	IsConflict
InternetCodePage	MeetingWorkspaceURL
Permission

Creating Filters for the Find and Restrict Methods

The syntax for the filter varies depending on the type of field you are filtering on.

String (for Text fields)

When filtering text fields, you can use either a pair of single quotes (') or a pair of double quotes (') to delimit the values that are part of the filter. For example, all of the following lines function correctly when the field is of type String :

In specifying a filter in a Jet or DASL query, if you use a pair of single quotes to delimit a string that is part of the filter, and the string contains another single quote or apostrophe, then add a single quote as an escape character before the single quote or apostrophe. Use a similar approach if you use a pair of double quotes to delimit a string. If the string contains a double quote, then add a double quote as an escape character before the double quote.

For example, in the DASL filter string that filters for the Subject property being equal to the word can't, the entire filter string is delimited by a pair of double quotes, and the embedded string can't is delimited by a pair of single quotes. There are three characters that you need to escape in this filter string: the starting double quote and the ending double quote for the property reference of http://schemas.microsoft.com/mapi/proptag/0x0037001f, and the apostrophe in the value condition for the word can't. Applying the appropriate escape characters, you can express the filter string as follows:

Alternatively, you can use the chr(34) function to represent the double quote (whose ASCII character value is 34) that is used as an escape character. Using the chr(34) substitution for a double-quote escape character, you can express the last example as follows:

Escaping single and double quote characters is also required for DASL queries with the ci_startswith or ci_phrasematch operators. For example, the following query performs a phrase match query for can't in the message subject:

Another example is a DASL filter string that filters for the Subject property being equal to the words the right stuff, where the word stuff is enclosed by double quotes. In this case, you must escape the enclosing double quotes as follows:

A different set of escaping rules apply to a property reference for named properties that contain the space, single quote, double quote, or percent character. For more information, see Referencing Properties by Namespace.

Date

Although dates and times are typically stored with a Date format, the Find and Restrict methods require that the date and time be converted to a string representation. To make sure that the date is formatted as Outlook expects, use the Format function. The following example creates a filter to find all contacts that have been modified after January 15, 1999 at 3:30 P.M.

Boolean Operators

Boolean operators, TRUE/ FALSE, YES/NO, ON/OFF, and so on, should not be converted to a string. For example, to determine whether journaling is enabled for contacts, you can use this filter:

Note

If you use quotation marks as delimiters with Boolean fields, then an empty string will find items whose fields are False and all non-empty strings will find items whose fields are True.

Keywords (or Categories)

The Categories field is of type keywords, which is designed to hold multiple values. When accessing it programmatically, the Categories field behaves like a Text field, and the string must match exactly. Values in the text string are separated by a comma and a space. This typically means that you cannot use the Find and Restrict methods on a keywords field if it contains more than one value. For example, if you have one contact in the Business category and one contact in the Business and Social categories, you cannot easily use the Find and Restrict methods to retrieve all items that are in the Business category. Instead, you can loop through all contacts in the folder and use the Instr function to test whether the string 'Business' is contained within the entire keywords field.

Note

A possible exception is if you limit the Categories field to two, or a low number of values. Then you can use the Find and Restrict methods with the OR logical operator to retrieve all Business contacts. For example (in pseudocode): 'Business' OR 'Business, Personal' OR 'Personal, Business.' Category strings are not case-sensitive.

Integer

You can search for Integer fields with or without quotation marks as delimiters. The following filters will find contacts that were created with Outlook 2000:

Using Variables as Part of the Filter

As the Restrict method example illustrates, you can use values from variables as part of the filter. The following Microsoft Visual Basic Scripting Edition (VBScript) code sample illustrates syntax that uses variables as part of the filter.

This approach uses Chr(34) to delimit the value:

This approach uses double quotation marks to delimit the value:

Using Logical Operators as Part of the Filter

Logical operators that are allowed are AND, OR, and NOT. The following are variations of the clause for the Restrict method, so you can specify multiple criteria.

OR: The following code returns all contact items that have either Business or Personal as their category.

AND: The following code retrieves all personal contacts who work at Microsoft.

NOT: The following code retrieves all personal contacts who don't work at Microsoft.

Additional Notes

If you are trying to use the Find or Restrict methods with user-defined fields, the fields must be defined in the folder, otherwise an error will occur. There is no way to perform a 'contains' operation. For example, you cannot use Find or Restrict to search for items that have a particular word in the Subject field. Instead, you can use the AdvancedSearch method, or you can loop through all of the items in the folder and use the InStr function to perform a search within a field. You can use the Restrict method to search for items that begin within a certain range of characters. For example, to search for all contacts with a last name beginning with the letter M, use this filter:

See also

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.