+ Reply to Thread
Results 1 to 16 of 16

index..match - issue

  1. #1
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    index..match - issue

    Hi, having some issue with my formula of index(match.

    I have attached a sample workbook. I'm trying to obtain the weekly sales or aka. in our company, nar , numbers for a few people. Here is my formula:

    Please Login or Register  to view this content.
    It's showing me a N/A. Now I'm sure I'm messing up defining the appropriate column reference but again, if someone can look at my workbook, i have highlighted in yellow the cell. Much appreciated.

    Cheers
    Attached Files Attached Files
    Last edited by NBVC; 04-29-2011 at 03:54 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    Re: index..match - issue

    Hi Step_one,

    Try using a VLookup formula instead of Index Match. Your table on Sheet P1 is not good in that it has merged cells for column heads and the same column names for many columns. This will keep you from finding what you need. The VLookup formula in the attached only needs the persons name and then count the number of columns over. It doesn't rely on duplicate column names.

    Hope this helps
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Prefer Excel 2003 but 2007 is great!
    Posts
    1,139

    Re: index..match - issue

    Hi Marvin. Jeez, I totally missed on a simple vlookup; got too caught up! Thanks for pointing out the obvious, thanks!

  4. #4
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index..match - issue

    I have the same workbook, but im trying to reference another worksheet (rather it will be another workbook on my server but for the purpose of this I have saved it in my workbook).

    Basically Im trying to obtain the revenue again for an individual. I have highlighted in yellow, the N/A Im getting from my index(match formula.


    I'm trying to obtain the revenue #s from worksheet "New Auto & Purge Revenue" and for this example, it's row 119. I need to show the revenue for W1, W2, W3, W4 (which is row 6 on that worksheet).

    here is my formula and attached is my workbook.

    =INDEX('New Auto & Purge Revenue'!$B$5:$H$222,MATCH($C7,INDEX('New Auto & Purge Revenue'!$B$5:$H$222,,1),0),MATCH(RIGHT(H6,2),INDEX('New Auto & Purge Revenue'!$B$5:$H$222,,,1),0))


    any thoughts, thank you.
    Attached Files Attached Files

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,275

    Re: index..match - issue

    Did you try the VLookup function I suggested?

  6. #6
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index..match - issue

    Hello Marvin

    Yes, but this is for another worksheet and it might require offset so i tried the index match.

    for instance, in my data worksheet "New Auto & Purge Revenue"
    the person's name is on row 115
    the data i need for i.e. W1 is on row 119, column C, W2 row 119 column D ...until W4.

    unless there is a vlookup(match? kindly advise, much thanks!

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: index..match - issue

    The right 2 characters of P1W1 don't match anything on the other sheet (i.e. W1 doesn't match Wk 1)... Also you don't really need the inner INDEXES and I used ROWS($A$!:$A1) to offset the rows by 1 after find the C7 name...

    Try:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  8. #8
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index..match - issue

    Brilliant, thank you for saving the day once again.

    Final question, I had to change up the row reference as I needed the 4th figure from the name or in this case row 119

    +ROWS($A1:$A$4)

    thank you for your kind help.

    CHeers
    Last edited by step_one; 04-29-2011 at 02:24 PM.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: index..match - issue

    If you are only looking for Actual Auto ($) Total only, then just hard-code an offset of 4 rows in the first MATCH():

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index..match - issue

    NBVC

    thx you for your prompt responses, your superb. Have a great weekend!
    Last edited by step_one; 04-29-2011 at 03:10 PM.

  11. #11
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index..match - issue

    Hi there

    Ive tried to incorporate the indirect.ext so I dont have to keep changing the "city" name however not sure if im placing something correctly or is my technique not correct?

    indirect.ext:
    =INDEX(INDIRECT.EXT("'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$H$222",false),MATCH(INDIRECT.EXT($B158,"'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$B$222",false),0)+ROWS($A$1:$A$4),MATCH(INDIRECT.EXT((REPLACE(RIGHT(I$6,2),2,0,"K "),"'[C_"&D159&"_P1W5_11.xls]New Auto & Purge Revenue'!$B$6:$H$6",false),0)))

    original formula with city name i.e. concord (replacing concord with D159
    =INDEX('[C_Concord_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$H$222,MATCH($B158,'[C_Concord_P1W5_11.xls]New Auto & Purge Revenue'!$B$5:$B$222,0)+ROWS($A$1:$A$4),MATCH(REPLACE(RIGHT(J$6,2),2,0,"K "),'[C_Concord_P1W5_11.xls]New Auto & Purge Revenue'!$B$6:$H$6,0))

    It's giving me a formula error? your thoughts? cheers

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: index..match - issue

    Try it like this:

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    04-27-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    113

    Re: index..match - issue

    NBVC

    Thanks for your wonderful help.

    It's a little strange that the indirect.ext formula above works when the path file is open. Otherwise it shows #REF error. Cant seem to get my head wrapped around that, even with the formula you have provided.

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: index..match - issue

    You need to include the full path in the formula in order for it to work with a closed workbook.

    e.g

    Please Login or Register  to view this content.
    Replace C:\Mydocs\ with your actual full path to the document.

  15. #15
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Prefer Excel 2003 but 2007 is great!
    Posts
    1,139

    Re: index..match - issue

    Sorry NBVC

    Was unable to get back to you earlier as we had a power outage for a few hours. I realized when I was working with the formula it rquired the path. Again, brilliant help, your phenomenal.

    Cheers mate.

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: index..match - issue

    Are you sure this is your thread, jw01? I am confused as you responded twice as if it was your issue.

+ 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