Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index Match Lookups (Excel 2000)

    Here is my dilemma (hope this isnt too confusing):

    I have sheet one with the following (I just attached an example):
    Table 1:
    Name T1 T2 T3
    Name One 2 3 4
    Name Two 5 6 7
    Name Three 1 8 9
    Name Four 1 2 3

    There are two problems. One, the table I need to compare has the names in a different format:
    Table 2:
    Name, O
    Name, Tw
    Name, Thr
    Name, F

    Two, the names in table 1can change. One time Name One may be missing, the next time Names One and Three may be gone. There is no static or constant order or range.

    I need to lookup the T1, T2, and T3 values based on the Table two names. I began by creating a relationship by creating a third Table:

    Table 3:
    Name 1 Name 2
    Name, O Name One
    Name, Tw Name Two
    Name, Thr Name Three
    Name, F Name Four

    Then I wrote the Index Match below:

    =INDEX(I$2:I$6,MATCH(PhoneTeamSummary!A8,G$2:G$4,F $2:F$6)) which says give me the T1 value because Name One = Name, O. It works, but now I think this is not the way to go because the list of names in Table 1 will continue to change, so how could I continue to keep a second table in line? I would have to match the Name One to the Name, O each time I imported the Table 1 data because it wont line up correctly due to the new list of names.

    How can I do a double lookup, ie, say "Here is my starting name, go find its brother in Table 2, then go find the T1 value for that brother in table 3"?

    I am not great with lookups. Can anyone provide a sample or example of a more advanced version of Index Match to do this?

  2. #2
    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: Index Match Lookups (Excel 2000)

    I would merge your "described table3" with your Table 2 (in sheet2) like this:
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center valign=bottom>1</td><td align=right valign=bottom>

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index Match Lookups (Excel 2000)

    Thanks. I didnt include the third sheet in case I was making it confusing, which I did even worse.

    Can you look at this revised excel (I put in some names) and run through with me once more? I added in the actual table I was considering. I definitely want to use a third table- once its fully built, I wont have to maintain it. Putting the names alongside like in example 2 wont work because the lits always changes names and name order. Once I build the lookup, I dont want to have to maintain it again.

    Thanks for your help. You got it but I am still having trouble applying it.

  4. #4
    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: Index Match Lookups (Excel 2000)

    As I mentioned, you need the columns in Table 3 in the opposite order for Vlookup to work
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>1</td><td>Name 2</td><td>Name 1</td><td align=center>2</td><td>Rodriquez, R.</td><td>Roberto Rodriguez</td><td align=center>3</td><td>McGuigan. R.</td><td>Robert Mcguigan</td><td align=center>4</td><td>Sharpe, N</td><td>Norma Sharpe</td></table>

    If you want to keep table3 in the column order in your workbook, you must use a different formula:
    <pre>=VLOOKUP(INDEX(Sheet3!$A$2:$A$4,MATCH(A3,Shee t3!$B$2:$B$4,0)),Sheet2!$A$1:$B$4,2,0)</pre>


    Steve

  5. #5
    Lounger
    Join Date
    Dec 2004
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index Match Lookups (Excel 2000)

    Ok, Thanks! My head was swimming with cells. That worked. Now Ill study it a bit. Conceptually, I have a lot of trouble understanding the Vloookup, even with the formula helper.

  6. #6
    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: Index Match Lookups (Excel 2000)

    Look at the formula in E3:
    =VLOOKUP(VLOOKUP(A3,Sheet3!$A$2:$B$5,2,0),Sheet2!$ A$1:$B$5,2,0)
    and break it apart.
    The first "subset" is
    VLOOKUP(A3,Sheet3!$A$2:$B$5,2,0)
    The zero (0) at the end indicates it will look for an exact match.
    so excel will look in Sheet3 cells A2 to A5 (the first column of Sheet3!$A$2:$B$5) for a match to A3. If it finds one, it will report the result of the 2nd column in Sheet3!$A$2:$B$5) which is column "B" in that row.

    A3 = "Rodriquez, R"
    A match is found in Sheet3!A2 so it grabs "Roberto Rodriguez" from Cell B2.

    Now we do the 2nd lookup which (based on above) is equivalent to:
    =VLOOKUP("Roberto Rodriguez", Sheet2!$A$1:$B$5,2,0)
    This works the same way. It looks for an exact match (0), of "Roberto Rodriguez" in the first column of Sheet2!$A$1:$B$5 (ie in Sheet2 cells A1 to A5) and if found will give the result of the 2nd column (ie Coumn [img]/forums/images/smilies/cool.gif[/img] of that row.
    "Roberto Rodriguez" is found in Sheet2! A4 so the result of Sheet2!B4 (80) is the result of the formula.

    Lookups always take the value and lookit up in the the first column of the range and report the results of the column indicated. Your last workbook had the Col order reversed and it could not find the items in column A (they were in Col [img]/forums/images/smilies/cool.gif[/img] and resulted in an error (no match). By reversing the columns, you lookup in a and result in B, then the item from Table3, col B is used to "lookup" in Table2.

    Hope that is clear.
    Steve

Posting Permissions

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