From: Jen_T on
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
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
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
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
>>