Solved

Index / Match Function

Posted on 2011-02-11
7
225 Views
Last Modified: 2012-05-11
Hi,

if you look at Column J of the attached file you can see it is picking up tax Credit from Column A (13.88 ETC)

I need this formula to be changed to pick up gross (one row up in ColA)

In K you can see it is picking up the holdings from ColA.

The ones i have highlighted re are incorrect because they dont pick up enough characters, so the formula needs to be adjusted to pick up numbers that would be a over 8 figures

If anyone can adjust for these two that would be great!

Thanks
Seamus
CP-Rep1-1-.zip
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34869952
Dear

=TRIM(MID(INDEX($A:$A;$B7+9);36;9)) in the first J cell

Kr

Eric
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34869963
for columns K to adjust the amount of numbers to be picked up you have to change

=TRIM(MID(INDEX($A:$A;$B9+20);36;10)) the last number in this formula The 10 indicates you want to copy 10 characters( numbers)

If you want more change into 11, 15 or 20...


KR

Eric
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 500 total points
ID: 34869970
Dear seamus,

I adjusted all cell in columns J and K

Kr

Eric
CP-Rep1-1-.xls
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:Seamus2626
ID: 34870007
Hi Zwiekhorst,

Thank you for that.

I get how it works now!
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34870017
But that still doesn't pick up the full figure, e.g. K9 should be 1,695,279, I believe. Each of the relevant cells appears to have a colon at position 22 then the rest of the data is spaces and the figure so to get the full figure you might as well take everything after character 22 and trim it, e.g. in K7 copied down

=TRIM(MID(INDEX($A:$A,$B7+20),23,100))

If you want to convert that to a number (it's currently text) then add +0 to the end of the formula

regards, barry
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34870043
Hi Seamus,

Thanks for the points and the grade

kr

Eric
0
 

Author Comment

by:Seamus2626
ID: 34870077
hey Barry, your right, but i worked it out from the Col J adjustment so all good, thanks for the update though!

Cheers,
Seamus
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Vlookup Help 3 29
need count all combination 31 33
Search functions & INDEX Match 11 22
Disable auto refresh in a sheet 2 15
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question