From: Jen_T on 15 May 2010 00:09 Is it possibly to look at cell value and if in a range to place the record # in a cell ? E.g. Value in A2 that I need to know if in a range "R1234JK3" RANGE Record # Proj # Billing # Review # 1 R1234JK3 KIKII87 KL12367 2 RM334 12456 0089JK_9 Ce;; Value returned in A3 "1" which stands for record 1
From: Bernard Liengme on 15 May 2010 08:50 I have interpreted this as follows; You have data such as Record # Proj # Billing # Review # 1 R1234JK3 KIKII87 KL12367 2 RM334 12456 0089JK_9 ..... I will assume this is in Sheet1 in cells A1:D100 On Sheet2 in cell A2 you have some text such as R1234JK3 In cell A3 of the same sheet you want to know the record number in which this text appears in column B (the Proj# column) of Sheet1 Assuming the record numbers are in order, this will give the answer =MATCH(A2,Sheet1!B:B)1 The reason for subtracting 1 is that the data begins with labels. If the labels had been in row 5, say( then we would use =MATCH(A2,Sheet1!B:B)5 If the record numbers are not in order (or some are missing) then use =INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B)) best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message news:D56153F47D664C8FA1B023853C66C0DB(a)microsoft.com... > Is it possibly to look at cell value and if in a range to place the record > # > in a cell ? > > E.g. > Value in A2 that I need to know if in a range "R1234JK3" > > RANGE > Record # Proj # Billing # Review # > 1 R1234JK3 KIKII87 KL12367 > 2 RM334 12456 0089JK_9 > > Ce;; Value returned in A3 "1" which stands for record 1
From: Jen_T on 15 May 2010 09:23 The records are not in order so thank you for the INDEX function. My other concern is that sometimes there is multiple proj #s and may include the one I am looking for within that range. How would one accompolish checking the range in these types of situations. Record # Proj # Billing # Review # > 1 R1234JK3, PRT456 KIKII87 KL12367 > 2 RM334 PRT456 12456 0089JK_9 > .... "Bernard Liengme" wrote: > I have interpreted this as follows; > You have data such as > Record # Proj # Billing # Review # > 1 R1234JK3 KIKII87 KL12367 > 2 RM334 12456 0089JK_9 > .... > I will assume this is in Sheet1 in cells A1:D100 > On Sheet2 in cell A2 you have some text such as R1234JK3 > In cell A3 of the same sheet you want to know the record number in which > this text appears in column B (the Proj# column) of Sheet1 > Assuming the record numbers are in order, this will give the answer > =MATCH(A2,Sheet1!B:B)1 > The reason for subtracting 1 is that the data begins with labels. If the > labels had been in row 5, say( then we would use =MATCH(A2,Sheet1!B:B)5 > If the record numbers are not in order (or some are missing) then use > =INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B)) > best wishes >  > Bernard Liengme > Microsoft Excel MVP > http://people.stfx.ca/bliengme > "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message > news:D56153F47D664C8FA1B023853C66C0DB(a)microsoft.com... > > Is it possibly to look at cell value and if in a range to place the record > > # > > in a cell ? > > > > E.g. > > Value in A2 that I need to know if in a range "R1234JK3" > > > > RANGE > > Record # Proj # Billing # Review # > > 1 R1234JK3 KIKII87 KL12367 > > 2 RM334 12456 0089JK_9 > > > > Ce;; Value returned in A3 "1" which stands for record 1 >
From: Bernard Liengme on 15 May 2010 14:47 I would not like to work with such a data set. I would split the double entries into two cells using Data  Text to Column best wishes  Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message news:501639517CBD4DB29310A7E5322FD9CC(a)microsoft.com... > The records are not in order so thank you for the INDEX function. My other > concern is that sometimes there is multiple proj #s and may include the > one I > am looking for within that range. How would one accompolish checking the > range in these types of situations. > > Record # Proj # Billing # Review > # >> 1 R1234JK3, PRT456 KIKII87 KL12367 >> 2 RM334 PRT456 12456 0089JK_9 >> .... > > > "Bernard Liengme" wrote: > >> I have interpreted this as follows; >> You have data such as >> Record # Proj # Billing # Review # >> 1 R1234JK3 KIKII87 KL12367 >> 2 RM334 12456 0089JK_9 >> .... >> I will assume this is in Sheet1 in cells A1:D100 >> On Sheet2 in cell A2 you have some text such as R1234JK3 >> In cell A3 of the same sheet you want to know the record number in which >> this text appears in column B (the Proj# column) of Sheet1 >> Assuming the record numbers are in order, this will give the answer >> =MATCH(A2,Sheet1!B:B)1 >> The reason for subtracting 1 is that the data begins with labels. If the >> labels had been in row 5, say( then we would use =MATCH(A2,Sheet1!B:B)5 >> If the record numbers are not in order (or some are missing) then use >> =INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B)) >> best wishes >>  >> Bernard Liengme >> Microsoft Excel MVP >> http://people.stfx.ca/bliengme >> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message >> news:D56153F47D664C8FA1B023853C66C0DB(a)microsoft.com... >> > Is it possibly to look at cell value and if in a range to place the >> > record >> > # >> > in a cell ? >> > >> > E.g. >> > Value in A2 that I need to know if in a range "R1234JK3" >> > >> > RANGE >> > Record # Proj # Billing # Review # >> > 1 R1234JK3 KIKII87 KL12367 >> > 2 RM334 12456 0089JK_9 >> > >> > Ce;; Value returned in A3 "1" which stands for record 1 >>

Pages: 1 Prev: Totalling Subtotals Less Than 1 Next: Excel Formula to colourfill cells when condition met 