Need to work on the formulas to get this data

Ahmed Fawad

Board Regular
Joined
Mar 1, 2011
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
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:

ClusterPropertyNameTypeCityREMARKS
2612
269
264

<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 ?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Formula in H2 copied down and across:


Excel 2010
ABCDEFGHIJ
1CodeNameTypeCityClusterPropertyNameTypeCity
226I337TAMWEELORGANIZATIONDUBAI2612Muhammad Asad IqbalPERSONDubai
326I338Al Wathba National Insurance Co.ORGANIZATIONABU DHABI269Faris Al-AzzawiPERSONAbu Dhabi
426I339Al Wathba National Insurance Co.ORGANIZATIONABU DHABI264Bassam A.A. ChilmeranPERSONABU DHABI
526I340Bassam A.A. ChilmeranPERSONABU DHABI
626I341Al Wathba National Insurance Co.ORGANIZATIONABU DHABI
726I342ZIAD OMAR ZIAD JAAFAR ALASKARIPERSONABU DHABI
826I343NAILA JAMAL MAJID BIN THENIYAPERSONDUBAI
926I344Landbanc LimitedORGANIZATIONDubai
1026I345Faris Al-AzzawiPERSONAbu Dhabi
1126I346Said Abdelhamid KayaliPERSONDubai
1226I347Amir Seyed Ali LajevardiPERSONDubai
1326I348Muhammad Asad IqbalPERSONDubai
1426I349Walid Abdulrahman AlturkiPERSONDubai
1526I350Al Wathba National Insurance Co.ORGANIZATIONABU DHABI
1626I351Al Wathba National Insurance Co.ORGANIZATIONABU DHABI
1726I352Zaid Fadhel Abdul Rassool AliPERSONDubai
Sheet1
Cell Formulas
RangeFormula
H2=INDEX(B$2:B$17,MATCH($F2&"*",$A$2:$A$17,FALSE)+$G2-1)
 

Ahmed Fawad

Board Regular
Joined
Mar 1, 2011
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
Its working fine... why $G2-1 ???
I think only +$G2 is fine....
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Similar threads

Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,878
Messages
5,834,200
Members
430,263
Latest member
abz54

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
Top