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

zzzjoshzzz

New Member
Joined
Apr 27, 2014
Messages
23
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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'?
 
Upvote 0
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)
 
Upvote 0
Hi.

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

Regards
 
Upvote 0
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
 
Upvote 0
Maybe something like this:


Excel 2010
ABCDEFGHIJKL
17LookupColumnRow
18Lamp1215
19
20ALBANYALPHARETTAJohn3000Honourable MentionABC1A71 - 30 days1 - 30 days1 - 30 days1 - 30 days
21ALPHARETTA31 - 60 daysAdam2500Honourable MentionDEF2C7Lamp1Lamp1Lamp1Lamp1
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)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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