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

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 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" wrote in message
...
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

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 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" wrote in message
...
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

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 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" wrote in message
...
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