# Get Column and Row of a Specified Iteration of a String Within a Specified Range.

#### zzzjoshzzz

##### New Member
Hi friends! I'd appreciate your help:

I'm looking for a way to get back, in two cells (one for column, one for row), the location of the first iteration of a string within a specified range (Like inside the rectangle formed by A20 and L40)of my excel sheet.

Thanks a lot! I look forward to seeing the responses.
PS I'm using Excel 2007

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
When you say a "string" are you talking about partial text within one cell, for instance look for the word 'car' in a cell that may say 'car wash' or "string" as in the value of the cell, in this case the whole text 'car wash'?

When you say a "string" are you talking about partial text within one cell, for instance look for the word 'car' in a cell that may say 'car wash' or "string" as in the value of the cell, in this case the whole text 'car wash'?

In this case, either will do. I'm looking to find the first iteration of Lamp1 for example. (Within a range of cells, not the whole sheet)

Hi.

What would be considered the "first iteration" in the range A20:L40 if Lamp1 was present in both cells A21 and B20?

Regards

Hi.

What would be considered the "first iteration" in the range A20:L40 if Lamp1 was present in both cells A21 and B20?

Regards
Hi, thanks for the response.

The first iteration would be A21, but if it's easier to code B20 first that's ok.

Thanks,
zzzjoshzzz

Maybe something like this:

Excel 2010
ABCDEFGHIJKL
17LookupColumnRow
18Lamp1215
19
20ALBANYALPHARETTAJohn3000Honourable MentionABC1A71 - 30 days1 - 30 days1 - 30 days1 - 30 days
22ATHENSSalesperson 1Eric200Serious Risk of Being KickedGHI3D7Lamp1Lamp1Lamp1Lamp1
23COLUMBUSSalesperson 1Haley1200Activity NeededJKL4E7Lamp1Lamp1Lamp1Lamp1
24DULUTHSalesperson 2Shaun0KICKMNO5F7Lamp1Lamp1Lamp1Lamp1
25GAINESVILLESalesperson 2Carlos4Serious Risk of Being KickedPQR6G7Lamp1Lamp1Lamp1Lamp1
26MACONSalesperson 3Diana500Activity Needed7Lamp1Lamp1Lamp1Lamp1
27MARIETTASalesperson 3Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
28ATLANTATransaction 1CarsLamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
29SAVANNAHTransaction 2Cars, TrucksLamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
30VALDOSTATransaction 3MotorcyclesLamp1Lamp1HELLOLamp1Lamp1Lamp1Lamp1Lamp1Lamp1
31VALDOSTATransaction 4TrucksLamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
32VALDOSTATransaction 5Cars, Trucks, MotorcyclesLamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
33VALDOSTATransaction 6TrucksLamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
34VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
35VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1JoshJohnLamp1Lamp1Lamp1
36VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
37VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1AndrewLamp1
38VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1AndrewLamp1Lamp1Lamp1
39VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
40VALDOSTALamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1Lamp1
Sheet1
Cell Formulas
RangeFormula
B18=AGGREGATE(15,6,1/(A20:L40=A18)*(COLUMN(A20:L20)-COLUMN(A20)+1),1)
C18=MATCH(A18,INDEX(A20:L40,,B18),0)

Replies
8
Views
247
Replies
1
Views
966
Replies
2
Views
206
Replies
18
Views
511
Replies
2
Views
320

1,196,254
Messages
6,014,273
Members
441,810
Latest member
LouLou1234

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back