+ Reply to Thread
Results 1 to 10 of 10

Multiple vlookup?

  1. #1
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    Multiple vlookup?

    I have a table that has two columns of data. The combinations of the data produce statements that need to be reflected in a third column. I know how to use vlookup to see one column. But what about 2 columns with multiple combinations? Attached is a table with the appropriate responses.

    The data is in columns H (the rows) and column L (the columns). The responses that need to be placed in a third column (column R) is in the cells in the table.

    Any ideas of how to set this up?

    Thanks. BTW, you can also email me.
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You'll probably need an Index/Match formula..

    see attached.

    Formula used:

    =INDEX($B$4:$E$7,MATCH(A12,$A$4:$A$7,0),MATCH(B12,B3:E3,0))

    Where A12 and B12 contain input values to lookup in table A4:E7
    Attached Files Attached Files
    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.

  3. #3
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    additional data in file

    My original zip file may not have given enough information. I am uploading a new one that has:

    1. The table as mentioned before

    2. The input table that shows a sample of columns H and L and what I need to have in column R.

    The table at the top shows how to figure out what needs to go into to column R, but the figures for columns H and L are in the table at the bottom of the page.

    Hope this makes sense!

    Will the index/match idea work with this?
    Attached Files Attached Files

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

    Note: First and Second headers look that are both 0%, but only the first is. The second is .0000000001....
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    not sure that will work

    Using the example in the REV file you gave, lets put it this way:

    1. Look at B14 -- the value is "0.80%"
    2. Now go to table and see that "0.80%" fits in row 5
    3. Look at C14 -- the value is 2.04%
    4 Now go to table and see that "2.04%" fits in column C
    5. Then Look at the table C5 and see that the value is "Some Mission Opportunity"
    6. Put the value "Some Mission Opportunity" in D14.

    In other words, the formula needs to be in D14 that says to do the above 6 steps. I tried the following formula, but it did not work:

    =INDEX($B$4:$E$7,MATCH(B14,$A$4:$A$7,0),MATCH(C14, $B$3:$E$3,0))

    Do you know what I did wrong?

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The formula I put in D14 of that file did exactly those 6 steps and returned the result you mentioned.

  7. #7
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    thanks

    I didn't see -- thanks so much for the help!!!

  8. #8
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    Additional vlookup problem

    I have added a vlookup to the spreadsheet -- see E14. This lookup is:

    =VLOOKUP(D14,$H$14:$I$26,2,TRUE)

    However, incorrect data shows up rather than the correct data. Any ideas why?

    See attachment
    Attached Files Attached Files

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Change the TRUE in the formula to FALSE.... this finds exact matches.

  10. #10
    Registered User
    Join Date
    10-28-2007
    Posts
    21

    Thanks!

    Thanks again -- sorry for the "dumb" questions -- but your expertise is greatly appreciated!

+ 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