+ Reply to Thread
Results 1 to 5 of 5

INDEX/LOOKUP solution

  1. #1
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2007
    Posts
    182

    INDEX/LOOKUP solution

    All,

    On the attached, on "Summary" tab, I want to return the value for the corresponding name for the headings supplied (entitlement, remaining,sick)

    The "Summary" tab should draw this info from the "Coleshill" or "Stratford" tab.

    I know I could simply equals the cell for the relevant name but I want to be able to add new names in and use the same formula.

    i.e. I want excel to look at the name on both sheets and return the relevant value for the three headings.

    Hope I'm making myself clear.

    Thanks.
    Attached Files Attached Files
    Last edited by johnmitch38; 10-26-2011 at 11:05 AM. Reason: Solved

  2. #2
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: INDEX/LOOKUP solution

    Hi johnmitch38,

    Please see attached. I think it is what you are looking for.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: INDEX/LOOKUP solution

    Hi quekbc, many thanks for the reply.

    Yes that's returning the correct result by the looks of things. However, i don't understand the formula.

    What is the relevance of the "R" numbers? Sorry to be a pain as youve been a real help but i like to get my head round why something works.

  4. #4
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: INDEX/LOOKUP solution

    No worries.

    I used R1C1 reference style for the INDIRECT function.

    This is indicated by a FALSE in the last parameter in the INDIRECT function. Saying R4 in R1C1 is like saying row 4, or 4:4 in A1 style. (I like to work in R1C1)

    You can change the reference style by doing...
    1) Change D3, E3 and F3 to 7:7, 9:9, and 5:5 respectively (make sure they are text)
    2) and change the formula from
    =IFERROR(INDEX(INDIRECT($C4&D$3,FALSE),MATCH($A4,INDIRECT($C4&"R4",FALSE),0)),0)
    to
    =IFERROR(INDEX(INDIRECT($C4&D$3),MATCH($A4,INDIRECT($C4&"4:4"),0)),0)

  5. #5
    Forum Contributor
    Join Date
    05-29-2009
    Location
    sheffield
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: INDEX/LOOKUP solution

    Think i've finally got it now thanks.

    I deleted a couple of rows and was struggling to figure out which reference i needed to changed.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1