Search for cell value

  • Thread starter Thread starter Jen_T
  • Start date Start date
J

Jen_T

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
 
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
 
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 #
 
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
 
Back
Top