Search for cell value
in [Functions]
|
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 "R1234JK-3" RANGE Record # Proj # Billing # Review # 1 R1234JK-3 KIKII-87 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 R1234JK-3 KIKII-87 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 R1234JK-3 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:D56153F4-7D66-4C8F-A1B0-23853C66C0DB(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 "R1234JK-3" > > RANGE > Record # Proj # Billing # Review # > 1 R1234JK-3 KIKII-87 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 R1234JK-3, PRT456 KIKII-87 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 R1234JK-3 KIKII-87 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 R1234JK-3 > 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:D56153F4-7D66-4C8F-A1B0-23853C66C0DB(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 "R1234JK-3" > > > > RANGE > > Record # Proj # Billing # Review # > > 1 R1234JK-3 KIKII-87 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:50163951-7CBD-4DB2-9310-A7E5322FD9CC(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 R1234JK-3, PRT456 KIKII-87 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 R1234JK-3 KIKII-87 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 R1234JK-3 >> 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:D56153F4-7D66-4C8F-A1B0-23853C66C0DB(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 "R1234JK-3" >> > >> > RANGE >> > Record # Proj # Billing # Review # >> > 1 R1234JK-3 KIKII-87 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 |