Results 1 to 4 of 4

Thread: Populating cells based on matching information

  1. #1

    Populating cells based on matching information



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    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..


  3. #3
    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 )
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,462
    Articles
    0
    Excel Version
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •