+ Reply to Thread
Results 1 to 9 of 9

Index/Match against multiple columns

  1. #1
    Registered User
    Join Date
    03-22-2012
    Location
    Oldsmar, Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Question Index/Match against multiple columns

    Hi everyone!

    Excel 2010

    I have this lovely little code that works perfectly:

    Please Login or Register  to view this content.
    Only problem is that it only does index/matching against the B12:B42 range... Ultimately, I need it to match against ranges D12:D42, F12:F42, and H12:H42.

    I've been looking around, and I'll be honest, I'm not great when it comes to the actual Excel 2010 formulas, but I'm getting REALLY good with the VBA stuff.

    If the above can be accomplished, I'm going to embed it into a macro; I'll be able to figure out the variables, its just the syntax to index/match against 4 columns total.

    Can this even be done???

    Thanks!
    Last edited by paradox34690; 04-28-2012 at 12:32 PM. Reason: Corrected "code" to eliminate confusion.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index/Match against multiple columns

    Hi paradox,

    MATCH(B12,'Top Sheet Generator'!B12:B42,0))

    This is matching B12 in range B12: B43 of sheet Top Sheet Generator.. now do you want to match B12 in D12:42, F12:F42 and H12:H42 of that sheet ?

    I think it would be better to understand if you could share the logic first.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    03-22-2012
    Location
    Oldsmar, Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Index/Match against multiple columns

    Quote Originally Posted by dilipandey View Post
    Hi paradox,

    MATCH(B12,'Top Sheet Generator'!B12:B42,0))

    This is matching B12 in range B12: B43 of sheet Top Sheet Generator.. now do you want to match B12 in D12:42, F12:F42 and H12:H42 of that sheet ?

    I think it would be better to understand if you could share the logic first.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Sorry, the "MATCH(B12..." is for B12 on a sheet named 'Logged Hours' (this formula will reside there)

    I'm matching the contents of $b:$b on 'Logged Hours' (for example) to find out what row it's in in columns D12:D42, F12:F42, and H12:H42 on the 'Top Sheet Generator'. When that reference is found in one of those columns, then use the value stored in specific row in column range A12:A14.

    Sorry, this is a very very in depth scheduling spreadsheet that i'm working on, many custom vba macros; appending data to an access database, creating archive'd PDF copies of the form on another worksheet, email routines, the works... I've been working on this for about a month or so and pretty much done. This and one more index/match routine for one more column and I'll finally be done! :D

    If that doesn't make sense, I'll try to explain better (hoping the above makes sense)

  4. #4
    Registered User
    Join Date
    03-22-2012
    Location
    Oldsmar, Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Index/Match against multiple columns

    I modified the code in my OP to reflect the worksheets and layouts.

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index/Match against multiple columns

    Hi Paradox,

    I'm matching the contents of $b:$b on 'Logged Hours'
    Contents of B:B or B12... i.e., a range or a cell ?
    because Match willl match a cell to a range.. and it will not match a range to a range.

    to find out what row it's in in columns D12:D42, F12:F42, and H12:H42 on the 'Top Sheet Generator'.
    Okay. if it would be only one match in the above ranges, then you can use the below logic:-

    If(isnumber(MATCH('Logged Hours'!B12,'Top Sheet Generator'!B12:B42,0),MATCH('Logged Hours'!B12,'Top Sheet Generator'!B12:B42,0),""

    Or

    if(isnumber(MATCH('Logged Hours'!B12,'Top Sheet Generator'!d12:d42,0),MATCH('Logged Hours'!B12,'Top Sheet Generator'!d12:d42,0),if(isnumber(MATCH('Logged Hours'!B12,'Top Sheet Generator'!F12:f42,0),MATCH('Logged Hours'!B12,'Top Sheet Generator'!F12:f42,0)....... and so on

    this will give you the one matched number which you can further use in your index function. Thanks.

    Any issue, upload the sample workbook.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  6. #6
    Registered User
    Join Date
    03-22-2012
    Location
    Oldsmar, Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Index/Match against multiple columns

    Dilipandey,

    I tried the code that you suggested; it was not successful.

    I'm uploading my spreadsheet; it is a Macro-Enabled .MXLS file. I have embedded VBA for the buttons located on various worksheets.

    The issue that I'm trying to solve involves the "Logged Hours" sheet.

    I'm modified this workbook so that there is data to work with; (this is part of a much bigger system and requires data from a specific data source. I cannot modify the layout either; what you see is what you get.

    Anyways, on the "Logged Hours" sheet, you'll notice a "DPA" and "Senior DPA" column; the current issue is figuring out the DPA for each item in the "Network" column. This can be found on the "Top Sheet Generator" tab. Note A12:H12; A12 is the "DPA"; B12, D12, F12, H12 are the networks assigned to that DPA (each of those boxes is a combobox that does data validation against $K:$K (for the associate names) and $M:$M (for the networks to be assigned).

    What I need to accomplish is to find a way to have the DPA column on Logged Hours reflect which associate is assigned each network in the "Network" column. In the upload that I'm attaching, I have an associate name in A12 and a total of 4 stations assigned in B12, D12, F12, H12 - When you see the workbook, this should make sense.

    The final part that I have to figure out is matching the Senior DPA (in $F:$F) to the Associate listed in $E:$E (on Logged Hours) and I could use help on this too; Senior DPA for A12:A14 = A11, Senior DPA for A16:A18=A15, Senior DPA for A20:A22 = A19, etc (you'll understand when you look) I suspect that I can't do this part at all until I can figure out the DPA/Network assignment issue.

    Once I find out how to do this properly, I'll be taking both of the equations and making them part of the "ProcessLoggingTime" VBA Macro so that it assigns the equation to all required cells at macro run-time because the list can be much longer or much shorter than what is currently listed.

    This needs to be complete for a big project that I've been working on for the last month or so and am rolling out this upcoming Tuesday...

    Thank you so much!
    Data Production Schedule - Uploadable copy.xlsm
    Last edited by paradox34690; 04-27-2012 at 10:03 PM. Reason: Added info about "ProcessLoggingTime" VBA Macro.

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index/Match against multiple columns

    Hi Paradox,

    See the attached file and let me know if this helps. thanks.
    Data Production Schedule - Uploadable copy.xlsm

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  8. #8
    Registered User
    Join Date
    03-22-2012
    Location
    Oldsmar, Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Index/Match against multiple columns

    You are amazing! :D thank you so much! Now just one last thing to figure out and this project is all done! :D

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Index/Match against multiple columns

    You are welcome Paradox...

    Cheers

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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