Excel Functions – 28 Today Im going to teach you how to use combination of Index & Match effectively then you may be able to compare it with VLookUp. Since now I may give you more combinations of different functions from time to time. Check the attached file herewith where you can see that a small data is given for the sake of simplicity obviously we may apply the same on a larger data too. Data is entered at the range of L2:O7 having Order ID, Product, Unit Price, and Quantity at M12 I have provided user a space to enter his/ her desirable product and then at N12 and O12 he/ she will get Unit Price and Quantity of the respective product but if this exists in the data in other words if user enter any product which is not available in the data then he/ she may get error. Lets read the combined functions carefully at N12 to get relevant Unit Price =INDEX(L3:O7,MATCH(M12,M3:M7,0),3) so we have given the range of data as first argument (Array) of the Index then second argument of Index (Row #) has been calculated by Match function [1st argument: LookUp Value of Match is M12 where user may give any product, 2nd argument: LookUp Array column having products, 3rd argument: 0 for exact match) and then third argument of Index (Column #) has been given 3 as Unit Price is the third column of source data. At O12 the same functions and ranges has been copied except column # has been taken 4 because 4th column of the source data have Quantity. You may check by changing different products at M12 and observe the results obviously if you give a product not in the source data then you will get error. You may notice that currently Ive referred cell having product name Plum in the source data because Plum occurred more than once therefore functions took only relevant numbers from the first entry even Ive referred second entry but match function is looking for the product name to be matched irrespective the position of reference.
Posted on: Thu, 23 Oct 2014 04:06:59 +0000
Trending Topics
Recently Viewed Topics
© 2015