Populating cells based on matching information

jaspal

New member
Joined
Jul 12, 2013
Messages
10
Reaction score
0
Points
0
I would like to populate table AC519:ck448 with information from the tables above (AC97:CK259)-

So info from AG99 would go into cell AD275, Ag225 into AD276, etc

I have tried the formula
=IFERROR(OFFSET(AG$96,MATCH($AC275,$AB$96:$AB$259,0)+COUNTIF($AC$273:$AC273,$AC24),0),"")

but this doesn't seem to work- does anybody know here im going wrong or maybe suggest something else.

Thanks
 

Attachments

  • JASPAL CASHFLOW FINAl3.xls
    374.5 KB · Views: 13

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try in AD275:

=IFERROR(INDEX($AG$96:$CN$259,MATCH($AC275,$AB$96:$AB$259,0)+MATCH(INDEX($AC$273:$AC275,MATCH(10^10,$AB$273:$AB275)+1),$AD$23:$AD$44,0)+1,MATCH(AD$271,$AG$85:$CN$85,0)),"")

copied down to AD280, then copied across all the columns.

then you can copy AD275 to CK280 and paste to AD283, then paste to AD291, etc to get all figures..
 

jaspal

New member
Joined
Jul 12, 2013
Messages
10
Reaction score
0
Points
0
HI NBVC

Thanks so much for your for your help -it worked perfectly but I had to make a slight change to the way the page was formatted (attached) and so im now only picking up some of the values.

How would i change the formula to adapt for this (Rent numbers are picked up correctly- but for the other figures it seems to be looking in the wrong place i. the info in AG283 should appear in AG275 )
 

Attachments

  • Help.xls
    357.5 KB · Views: 14

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
A couple of things first....

1.. Insert a row below row 97 for Rent... this is to be consistent with the rest of the tables (i.e. 2 blank rows below the AB column item

2.. In AB157 you have some extra non printable characters at the end of the text string (Dilaps Inwards)... remove those

Now change formula in AG275 to:

=IFERROR(INDEX($AG$94:$AN$261,MATCH($AF275,$AB$94:$AB$261,0)+MATCH(INDEX($AF$273:$AF275,MATCH(10^10,$AE$273:$AE275)+1),$AD$23:$AD$44,0)+2,MATCH(AG$271,$AG$85:$AN$85,0)),"")

copied across and down, then copy to each section below.
 
Top