|
Lookup last value
I have a list which gets updated on daily basis.
It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya |
Lookup last value
Check out this page:
http://www.cpearson.com/excel/lookups.htm HTH, JP On Nov 2, 7:02 am, Madiya wrote: I have a list which gets updated on daily basis. It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya |
Lookup last value
Hi,
From your description it's not clear what your trying to do but this may help =INDEX(A1:B20,SMALL(IF(A1:B20=C1,ROW(A1:B20)-ROW(A1)+1,ROW(B20)+1),COUNTIF(A1:A20,C1)),2) Takes the value in C1 and finds the last instance of that value in the range A1:A20 and returns the corresponding value from column B. It's an array so enter with Ctrl+Shift+Enter Mike Mike "Madiya" wrote: I have a list which gets updated on daily basis. It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya |
Lookup last value
Try this ARRAY formula which must be entered using ctrl+shift+enter
=MAX(IF(A1:A21="aa",B1:B21)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message oups.com... I have a list which gets updated on daily basis. It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya |
Lookup last value
On Nov 2, 5:45 pm, "Don Guillett" wrote:
Try this ARRAY formula which must be entered using ctrl+shift+enter =MAX(IF(A1:A21="aa",B1:B21)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message oups.com... I have a list which gets updated on daily basis. It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya- Hide quoted text - - Show quoted text - Thank you all for your help. JP and Mike, Your solution works for fixed no of rows. My data keeps updating and no of rows also will go on increasing. Hance I need to adjust your formula for dynamic rows. will need your help on this as still I am not proficient in such complex formulas. Don, Your formula return the old value and not the latest values. I want exactly opposit. I will try MIN instead of MAX in your formula and post back. Regards, Madiya |
Lookup last value
Hello,
If your names are in column A and the values in B: =LOOKUP(2,1/("lookupname"=A1:A999),B1:B999) Regards, Bernd |
Lookup last value
My mistake. I misunderstood that you wanted the maximum in B if A ="aa"
Use this ARRAY instead =INDEX(B1:B20,MAX(IF(A1:A20="aa",ROW(A1:A20)))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message ups.com... On Nov 2, 5:45 pm, "Don Guillett" wrote: Try this ARRAY formula which must be entered using ctrl+shift+enter =MAX(IF(A1:A21="aa",B1:B21)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message oups.com... I have a list which gets updated on daily basis. It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya- Hide quoted text - - Show quoted text - Thank you all for your help. JP and Mike, Your solution works for fixed no of rows. My data keeps updating and no of rows also will go on increasing. Hance I need to adjust your formula for dynamic rows. will need your help on this as still I am not proficient in such complex formulas. Don, Your formula return the old value and not the latest values. I want exactly opposit. I will try MIN instead of MAX in your formula and post back. Regards, Madiya |
Lookup last value
Hi,
Extend the range like this. It doesn't matter if it's querying empty rows. =INDEX(A1:B65535,SMALL(IF(A1:B65535=C1,ROW(A1:B655 35)-ROW(A1)+1,ROW(B65535)+1),COUNTIF(A1:A65535,C1)),2) Mike "Madiya" wrote: On Nov 2, 5:45 pm, "Don Guillett" wrote: Try this ARRAY formula which must be entered using ctrl+shift+enter =MAX(IF(A1:A21="aa",B1:B21)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Madiya" wrote in message oups.com... I have a list which gets updated on daily basis. It means that the name will appear on the sheet daily and it keeps repeating. When I use standerd lookup, it retrives the value of the first match. I want to lookup the name and retrive the latest value. Regards, Madiya- Hide quoted text - - Show quoted text - Thank you all for your help. JP and Mike, Your solution works for fixed no of rows. My data keeps updating and no of rows also will go on increasing. Hance I need to adjust your formula for dynamic rows. will need your help on this as still I am not proficient in such complex formulas. Don, Your formula return the old value and not the latest values. I want exactly opposit. I will try MIN instead of MAX in your formula and post back. Regards, Madiya |
| All times are GMT +1. The time now is 07:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com