Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Matching or indexing? (Excel 2003)

    Good morning...Can someone help me with a 'matching' or 'indexing' formula? I am attaching a small sample WB...in my real workbook, the data in range A1:E5 (the yes, no , maybe, yes/no etc) is on 1 sheet; the data shown in range H5:L8 is on a different sheet in the same WB. I need to match the data from B2:E5 with the corresponding cells in I5:L8. Presently, I am using an OFFSET formula but I suspect that INDEX or MATCH might be better, especially b/c as employees are added (eg: employee A) or deleted (eg: employee D), the names and their relative positions will sort & re-alphabetize, and so the OFFSET formula goes out of whack. I have tried VLOOKUP and HLOOKUP, but these require reference to row or column numbers, and that means making about 250 edits to any formula that I fill down with b/c each date is a 'different' row (or column). If there is a way to have the row/column reference change as I fill down a VLOOKUP or HLOOKUP formula, that would likely also work, but I can't see how to make that adjustment occur. I hope that this is sufficient information to explain what I am trying to do...thank you.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching or indexing? (Excel 2003)

    One option is to select I5:L8, enter the formula =TRANSPOSE(B2:E5) and confirm with Ctrl+Shift+Enter to make it an array formula.

    Or enter this formula in I5:

    =INDEX($B$2:$E$5,MATCH(I$4,$A$2:$A$5,0),MATCH($H5, $B$1:$E$1,0))

    and fill down, then right.

    You can hide zero values in Tools | Options or by using a custom number format.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    Hi Hans....thanks for that....will both of these still work if the order of names changes? And, I seem to recall that array formulae seem to take longer to 'calculate', especially if there is a lot of data? Is that correct;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching or indexing? (Excel 2003)

    The TRANSPOSE formula assumes that the order of the names in both ranges is the same. The INDEX/MATCH formula doesn't depend on the order of the names or dates.

    (The TRANSPOSE array formula is in fact more efficient than the INDEX/MATCH formula - it doesn't compute anything, just switches the role of rows and columns)

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    OK...great;thank you for that. I will study what you have given me and get back to you.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    Good evening, Hans....so far, the INDEXING formula works very nicely. I would like to use the same thing to match data coming from another page (where I have shown daily job site assignments for employees)....I keep getting a #N/A value and I'm wondering if you could take a look at my formula...I suspect that I am trying to match the wrong data, but I'm not sure. I have attached an updated sample and you'll see that I am trying to match data from B10:E13 (daily job site assignments) with the EMPLOYEE in columns I, K, M and O.....
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching or indexing? (Excel 2003)

    I don't understand your setup and what you're trying to do. Could you explain in detail?

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    Hmm..OK...I am attaching a different 'view' of the previous WB. On the "Job site" sheet, I can make work assignments; on the "Notes" sheet, I add daily reminders about stuff that pertains to any employee, on any given day.

    The "Employee" sheet is what is seen by the various employees, and they can see their names in row 4, and their assignments in columns B, D, F, H...and their notes in columns C, E, G, I.
    I have placed explanatory notes on each page that should explain in much more detail what I am trying to do and how I am trying to link the pages; in actual fact, these sheets have a few dozen names and job sites (and cover a year) so I pretty much have to put stuff on different pages).
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Matching or indexing? (Excel 2003)

    I'd dump the Job site, Notes and Weekly sheets, and simply enter all information in the Employee sheet. You'll have all relevant information in one place, and you won't need any formulas.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Matching or indexing? (Excel 2003)

    If I understand, in Employee Sheet F5:
    =INDEX(Weekly!$A$2:$A$5,MATCH(G$4,(INDEX(Weekly!$B $2:$E$5,0,MATCH($A5,Weekly!$B$1:$G$1,0))),0))

    Copy down the column.

    Copy Col F to H, J, etc.

    I get different values than you list in F7 and F8 so perhaps I don't understand. I get Site 1 in F7 since Empl A is listed there for that week Site 3 in F8. I am not sure where you get the Site 3 and Site 4 [I presumed that you made the error not me] If it is my mistake could you elaborate on the logic to get site 3 and 4 in F7 and F8?

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    Hi Steve...yes, that was an error by me when entering the data manually....so far, your formula seems to work nicely and the conditional formatting works as well....also, I can now sort alphabetically when an employee leaves or joins and all the data seems to stay together.....I will experiment a bit more on my actual worksheet and let you know if everything holds true on a bigger, real-life scale....

    PS: Is it possible to use an INDEX formula on the Weekly sheet in rows 8-13 that will import the data from the Job site sheet (rather than using a LOOKUP formula) b/c then I could dump the sheet that contains the LOOKUPs and make my WB smaller..??..??

  12. #12
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    ???....which sheet are you tlaking about, b/c I5 is only used on the Employee sheet.....if you're tlaking about the Weekly, I5 is empty..??..I am confused...

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Matching or indexing? (Excel 2003)

    In Weekly B8 enter:
    =INDEX('Job site'!$B$2:$E$7,MATCH(B$7,'Job site'!$A$2:$A$7,0),MATCH($A8,'Job site'!$B$1:$E$1,0))

    Copy it down the column and across the rows.

    Steve

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Matching or indexing? (Excel 2003)

    It is the Notes sheet (the only sheet in the workbook. [It was in reply to your <post:=693,334>post 693,334</post:> and the workbook in that sheet, not to your <post:=693,394>post 693,394</post:> ...]

    Steve

  15. #15
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Matching or indexing? (Excel 2003)

    Ahh....ok...not I'm not confused.......and thank you for the previous post re: the Weekly...I will try that and let you lknow

Page 1 of 2 12 LastLast

Posting Permissions

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