Monday, 15 October 2012

How to Look up data from Right to Left using Excel Index Match VLookUp Function

Excel Index Match VLookUp Function with Multiple Criteria to Look Up Right to Left
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.

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.
{=INDEX (C2:C10, MATCH (1,(E2:E10=C13)*(D2:D10=D13),0),0)}

>> 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.
>>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 ; Business Intelligence & Analytics Professional

1 comment:

  1. Nice post. I was checking constantly this blog
    and I am impressed! Very useful info particulary the last part :)
    I care for such info much. I was looking for this particular information for a long time.
    Thank you and best of luck.


Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts