Monday, 15 October 2012

Excel Index Match VLookUp Function with Multiple Criteria to LookUp Right to Left

Look Up Based on Multiple Criteria using Index - Match Functions
We can use Index-Match Functions combination to lookup a desired value in a range (list of data) based on multiple criteria.
By Using INDEX and Match Functions Combination we can lookup up a value in any direction of the Table of Data.
In this Formula you can specify any no. of criteria based on your requirement.

This Formula vs. VLOOKUP
The Vlookup Function only searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. .

.i.e., VLookup Function works in Left-Right direction only.

Example-I:
Let us suppose there are three Parent Companies (say 123, 143, 456) which have three different subsidiaries in three different countries (say India, China, America).
Suppose now if you want lookup a Subsidiary Name of a Particular Parent in the Particular Country we can find as in the following manner.

FORMULA:

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

Here:

>> C2:C10 is the range where you have the subsidiary names, from which you want extract/lookup, one that is in the index of a selected Parent Co. and Country.
>> E2:E10 is the range where you have the Parent Companies Names.
>> D2:D10 is the range where you have the Country Names.
>> MATCH (1, (E2:E10=C13)*(D2:D10=D13), 0), is the Match Function which refers to row number of the Index Function. Here  if the value in E2:E10 is equals/matches to ‘C13’ and(* refers to AND) D2:D10 equals/matches to ‘D13’ then value in the corresponding row of C2:C10 is the Lookup value.

In this Formula you can specify any no. of criteria based on your requirement.

Note:
>>The above formula is an Array formula so that we should press ‘Ctrl+ Shift+ Enter’ to get the desired result otherwise we will get an  error ‘#VALUE’.




Thanks,
TAMATAM

1 comment:

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts