If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Search for cell value
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 |
#2
|
|||
|
|||
Search for cell value
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 A1100 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" wrote in message news 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 |
#3
|
|||
|
|||
Search for cell value
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 A1100 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" wrote in message news 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 |
#4
|
|||
|
|||
Search for cell value
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" wrote in message ... 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 A1100 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" wrote in message news 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 |
Thread Tools | |
Display Modes | |
|
|