• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting Last date in a range

vijay.vizzu

vijay.vizzu

Member
Hi...

I am vijay from Noida(India). I have an problem that, i have the large range of data, when i want to fetch the data from another sheet using vlookup function, it fetchs the first come value of the criteria and not go for last value of the criteria. Basically this one is Date value. so which function can fetch the last value of the criteria we mentioned.


Thanks in advance
 
Hi Vijay ,


Let us assume your data is in column A , from A5 through A100.


Suppose your lookup value is in cell B1.


To get the row number of the last occurrence of the lookup value , in the range A5:A100 , use the following formula , in any unused cell , say C1 :


{=MAX(ROW(5:100)*((A5:A100)=B1))}


The curly braces are just to show that it is entered as an array formula , using CTRL SHIFT ENTER.


The value returned will be the actual row number where the last ocurrence of the lookup value is to be found ; thus if the cell is A57 , the return value will be 57. Of course , you need to decide what should happen if the searched for value is not found.


Is this what you are looking for ?


Narayan
 
Thank you so much narayan..., but how can i link this formula with another sheet. my lookup value is in "sheet1" and i want to fetch the criteria value from "sheet2" in defined range. So.. how? i think u got my point


Thanks
 
Hi Vijay ,


Can you give the specific cell addresses ?


1. In the formula , in my earlier post , the lookup value is in cell B1. Can you give the full cell address of your lookup value e.g. 'Sheet1'!D23 ; this address should go in the place of B1 in the earlier formula.


2. Can you give the full address of the defined range in Sheet2 from where you want to fetch the criteria value ; once this is know , you can use the return row value from the earlier formula , to retrieve the value.


Narayan
 
Hi Narayan,


Now i am unsing this function "=IF(ISERROR(VLOOKUP(I1558,Drwg_014,6,FALSE)),"",VLOOKUP(I1558,Drwg_014,6,FALSE))", it fetchs value correct value, but whenever the criteria has two values, assume that 1,2,3. the current function fetch the value 1 not 3. i need value 3. i mean to say, if the criteria has multiple values, then it should bring the latest value (latest date).
 
range "='1GC-Drwg'!$B$3:$G$1500" in this 1GC-Drwg is my sheet name. I just want to give more clear in this problem. i want the lookup value should be check & count in the range, if it is one then get the value from the desired column, if it is 2 or 3 or 4 like this, then it should return the maximum count value, in this case 4, then it has to get that value of the desired column,


I Don't know how to attach the file, so ican't able to sent this file.
 
Hi Vijay ,


What happens if you use this formula , with the appropriate Sheet names added to the range addresses ? Do you get the maximum row number or do you get an error value ?


{=MAX(ROW(3:1500)*((B3:G1500)=I1558))}


About your comment on 1 , 2 , 3 or 4 , I am confused :


1. In which cell is this value put ?


2. What are the actions required if the value is 1 ? What are the actions required if the value is 2 , 3 or 4 ?


Can you specify in a lot of detail ?


Narayan
 
Hi.. Narayan,


I have 2 sheets, one is named as "014 (2)" & "1GC-Drwg". Basically this one is part cost information from the vendors.There are so many columns (from Colum A to AZ).Among them one of the column is named as "Drwg. recv.Dt". This data is to be fetch from my 2nd sheet "1GC-Drwg". The main criteria is "Part No" something like this (1GC-E1191-00)which is in my 1st Sheet "014 (2)".Now i have to put Drwg. recving information against this criteria. So, i have used Vlook up function. Its works fine. But the main problem starts from here. In 2nd Sheet, the criteria has it has more than 7 records with different receving dates. Now i have to put latest date which received last.So i think now you can able to understand my query.


Thanks
 
Hi Vijay ,


Do you think you can go through the following link and see if it helps ?


http://www.exceluser.com/explore/last-item-in-list.htm


Narayan
 
You must log in or register to reply here.
Back
Top