Hi Friends,

Looks like a lot of people are not comfortable with VLOOKUP function, let me try and make things simple,

Say you have a table of employee records. The first column is an employee number, and the remaining columns are various data about the employee. Any time you have an employee number in the worksheet, you can use VLOOKUP to return a specific datum about the employee. The syntax is VLOOKUP(value,data range,col no.,FALSE). It says to Excel, "Go to the data range. Find a row that has (value) in the first column of the data range. Return the (col no.)th value from that row. Once you get the hang of it, it is very simple and powerful.

Hope this helps!

Hi Yayati,

i m sorry i forgot to mention the MATCH function, you have to use INDEX and MATCH together to get the result.

It looks complicated, but its not. here is the syntax. (i hope u are very familiar with VLOOKUP as i would be using the same terms)

=INDEX(tablearray,MATCH(lookup_value,lookupcol),returncol)

where-

INDEX = it is a function, use as it is
tablearray= it is the table of your original data, refer it the same way as you would do in VLOOKUP
MATCH = it is a function, use as it is
lookup_value = the value that should get looked up in the data , the same way as you would do in VLOOKUP
lookupcol= this is the col in the tabble array in which you have the look_up value
returncol = this is the coloumn number of the data that u are trying to retrieve

as i said before, it looks complicated, but its not.

Let me know if you still have issues.

Hi Yayati,

if you have now gained good confidence over INDEX and MATCH , we can go a step further to get that "date for max qty..."

The entire scene remains the same, only difference is rather tahn providing the lookup_value , you will make excel compute that by using MAX function. therefore you existing formula as above   ""=INDEX(tablearray,MATCH(lookup_value,lookupcol),returncol)"", would change to -
 

=INDEX(tablearray,MATCH(MAX(arraycontaining qty),lookupcol),returncol)
 

This will give you the required result.

Let me know if you are not able to get around this.

Hi Satyanarayana,

I hope my above posts resolve your querry.

how to express figures in words ?

thanks

hi Khaleel,

thanks man. u r really good in excel. match function with index, worked for me perfectly. this is the ans to satyanarayana.d query also.

thanks  a lot,

yayati

Dear Yayati tyagi garu,

Good morning sir, How u get answer for my question like u "for which date having highest Quantity .

=INDEX(tablearray,MATCH(MAX(arraycontaining qty),lookupcol),returncol)
my colums are M contains qty, A contains Date.

kindly give solution for my querry.

Dear Yayati tyagi garu,

Good morning sir, How u get answer for my question like u "for which date having highest Quantity .

=INDEX(tablearray,MATCH(MAX(arraycontaining qty),lookupcol),returncol)
my colums are M contains qty, A contains Date.

kindly give solution for my querry.

Hi S.D.,

use this formula. change the rage according to ur need. i hope u can do this. if can't reply back.

=INDEX($A$8:$M13,MATCH(MAX($M$8:$M13),$M$8:$M13,0),1)

yayati

Dear Khaleel,

Thanks lot for your iniative to bring excell advancement.

One query please.

How to derive multiple row data by vlookup/or any relevant formulas as mentioned below:-

Input is single Value: Say Mumbai Location

Sheet1

Location:Mumbai

Sheet2 (Master Data)

Thanks a lot

Query 2

On what circumstances we are using index and match function.

Regards,

Lakshminarayanan

Dear Yayati Tyagi Sir,

I am very happy to tell the Thanks to u and CACLUBINDIA.COM, but I have some doubt, u r not telling the where the required data range. eg: A column Date, M column Qty , always u r using only qty reference , ofcourse answer has came. How it will came without giving A cell reference. Kindly explain in detail to my mail id :Sathyadharani @ gmail.com.

hi S.D.,

in formula, there is "1" in the last, it refers to coloumn no. in given range from which result have to extract. in your case coloumn 1 is of date, so formula is giving date as ans. if you change that no. to  2  or 3 etc, it will extract value from that cloumn i.e. B or C etc.

yayati