Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Index / Match Function


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!

Avatar of Eric Zwiekhorst
Eric Zwiekhorst
Flag of Netherlands image


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


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


Avatar of Eric Zwiekhorst
Eric Zwiekhorst
Flag of Netherlands image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seamus2626


Hi Zwiekhorst,

Thank you for that.

I get how it works now!
Avatar of barry houdini
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


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

regards, barry
Hi Seamus,

Thanks for the points and the grade


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