Need to work on the formulas to get this data

[Ahmed Fawad]

Hi

here is the table

26I337	TAMWEEL	ORGANIZATION	DUBAI
26I338	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
26I339	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
26I340	Bassam A.A. Chilmeran	PERSON	ABU DHABI
26I341	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
26I342	ZIAD OMAR ZIAD JAAFAR ALASKARI	PERSON	ABU DHABI
26I343	NAILA JAMAL MAJID BIN THENIYA	PERSON	DUBAI
26I344	Landbanc Limited	ORGANIZATION	Dubai
26I345	Faris Al-Azzawi	PERSON	Abu Dhabi
26I346	Said Abdelhamid Kayali	PERSON	Dubai
26I347	Amir Seyed Ali Lajevardi	PERSON	Dubai
26I348	Muhammad Asad Iqbal	PERSON	Dubai
26I349	Walid Abdulrahman Alturki	PERSON	Dubai
26I350	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
26I351	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
26I352	Zaid Fadhel Abdul Rassool Ali	PERSON	Dubai

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

thats the data and here I'm doing input to get the details:

Cluster	Property	Name	Type	City	REMARKS
26	12
26	9
26	4

<tbody>
</tbody>

Now from data from data LOCATION CODE will be cluster # and 12th entry
i.e. cluster 26 property 12 is the 12th Entry of series starting from digit 26.
Now i need the name of the customer from 12th entry of series starting from 26 locationcode. every series has 16 entries.


Any help ?

 

[steve the fish]

Havent you already asked this??

 

[Andrew Poulsom]

Formula in H2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Code	Name	Type	City		Cluster	Property	Name	Type	City
2	26I337	TAMWEEL	ORGANIZATION	DUBAI		26	12	Muhammad Asad Iqbal	PERSON	Dubai
3	26I338	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI		26	9	Faris Al-Azzawi	PERSON	Abu Dhabi
4	26I339	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI		26	4	Bassam A.A. Chilmeran	PERSON	ABU DHABI
5	26I340	Bassam A.A. Chilmeran	PERSON	ABU DHABI
6	26I341	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
7	26I342	ZIAD OMAR ZIAD JAAFAR ALASKARI	PERSON	ABU DHABI
8	26I343	NAILA JAMAL MAJID BIN THENIYA	PERSON	DUBAI
9	26I344	Landbanc Limited	ORGANIZATION	Dubai
10	26I345	Faris Al-Azzawi	PERSON	Abu Dhabi
11	26I346	Said Abdelhamid Kayali	PERSON	Dubai
12	26I347	Amir Seyed Ali Lajevardi	PERSON	Dubai
13	26I348	Muhammad Asad Iqbal	PERSON	Dubai
14	26I349	Walid Abdulrahman Alturki	PERSON	Dubai
15	26I350	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
16	26I351	Al Wathba National Insurance Co.	ORGANIZATION	ABU DHABI
17	26I352	Zaid Fadhel Abdul Rassool Ali	PERSON	Dubai
Sheet1

Cell Formulas
Range	Formula
H2	=INDEX(B$2:B$17,MATCH($F2&"*",$A$2:$A$17,FALSE)+$G2-1)

 

[Ahmed Fawad]

Steve, that was different....

 

[Ahmed Fawad]

Its working fine... why $G2-1 ???
I think only +$G2 is fine....

 

[Andrew Poulsom]

MATCH returns the position of the lookup_value, 1 in the example. So to return the 12th item you need to add 12 and deduct 1.