|
Lookup Formular on Table
HI all.
Is there a formular that I can put in the last column " Last came" that will look along the row for the last data entry that a Boy attended and bring back the date on the top label? So For example Harry's last attendance was in Jan-01, where as William last came in Apr-01. I have done this via a Macro but was hoping to use a formular instead. You help is appreciated Denz Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last Came Tom 1 1 1 1 May-01 Harry 1 Jan-01 William 1 1 1 Apr-01 James 1 1 May-01 Peter 1 1 1 Mar-01 |
Lookup Formular on Table
Hi
In G" enter =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) and copy down -- Regards Roger Govier wrote in message ... HI all. Is there a formular that I can put in the last column " Last came" that will look along the row for the last data entry that a Boy attended and bring back the date on the top label? So For example Harry's last attendance was in Jan-01, where as William last came in Apr-01. I have done this via a Macro but was hoping to use a formular instead. You help is appreciated Denz Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last Came Tom 1 1 1 1 May-01 Harry 1 Jan-01 William 1 1 1 Apr-01 James 1 1 May-01 Peter 1 1 1 Mar-01 |
Lookup Formular on Table
That should have read
Enter in G2 =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi In G" enter =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) and copy down -- Regards Roger Govier wrote in message ... HI all. Is there a formular that I can put in the last column " Last came" that will look along the row for the last data entry that a Boy attended and bring back the date on the top label? So For example Harry's last attendance was in Jan-01, where as William last came in Apr-01. I have done this via a Macro but was hoping to use a formular instead. You help is appreciated Denz Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last Came Tom 1 1 1 1 May-01 Harry 1 Jan-01 William 1 1 1 Apr-01 James 1 1 May-01 Peter 1 1 1 Mar-01 |
Lookup Formular on Table
On 27 Apr, 12:05, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote: That should have read Enter in G2 =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi In G" enter =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) and copy down -- Regards Roger Govier wrote in message ... HI all. Is there a formular that I can put in the last column " Last came" that will look along the row for the last data entry that a Boy attended and bring back the date on the top label? So For example Harry's last attendance was in Jan-01, where as William last came in Apr-01. I have done this via a Macro but was hoping to use a formular instead. You help is appreciated Denz Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last Came Tom 1 1 1 1 May-01 Harry 1 Jan-01 William 1 1 1 Apr-01 James 1 1 May-01 Peter 1 1 1 Mar-01- Hide quoted text - - Show quoted text - Roger You are a star - Thanks for saving me so much time. |
Lookup Formular on Table
On 15 May, 15:33, wrote:
On 27 Apr, 12:05, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: That should have read Enter in G2 =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) -- Regards Roger Govier "Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ... Hi In G" enter =INDEX($B$1:$F$1,MATCH(LOOKUP(99^99,B2:F2),B2:F2)) and copy down -- Regards Roger Govier wrote in message .... HI all. Is there a formular that I can put in the last column " Last came" that will look along the row for the last data entry that a Boy attended and bring back the date on the top label? So For example Harry's last attendance was in Jan-01, where as William last came in Apr-01. I have done this via a Macro but was hoping to use a formular instead.. You help is appreciated Denz Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last Came Tom 1 1 1 1 May-01 Harry 1 Jan-01 William 1 1 1 Apr-01 James 1 1 May-01 Peter 1 1 1 Mar-01- Hide quoted text - - Show quoted text - Roger You are a star - Thanks for saving me so much time.- Hide quoted text - - Show quoted text - Have encounterd a small problem When I have numbers in March, blank in April and numbers in May it is resulting in march rather than May? * Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last came Tom 1 1 1 1 1 May-01 Harry 1 1 1 1 Mar-01 William 1 1 1 Mar-01 James 1 1 Mar-01 Peter 1 1 Apr-01 |
Lookup Formular on Table
Hi
Try instead the array entered formula {=MAX(ISNUMBER(B2:F2)*$B$1:$F$1)} =MAX(ISNUMBER(B2:F2)*$B$1:$F$1) <=== Formula to be copied To enter or amend an array formula, use Control+Shift+Enter (CSE), not just Enter. When you use CSE, Excel will insert the curly braces { } around the formula. Do not enter the braces yourself. Copy the line I have pointed at, Paste it into the cell on your sheet and use CSE -- Regards Roger Govier Have encounterd a small problem When I have numbers in March, blank in April and numbers in May it is resulting in march rather than May? Jan-01 Feb-01 Mar-01 Apr-01 May-01 Last came Tom 1 1 1 1 1 May-01 Harry 1 1 1 1 Mar-01 William 1 1 1 Mar-01 James 1 1 Mar-01 Peter 1 1 Apr-01 |
| All times are GMT +1. The time now is 03:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com