+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP to search multiple columns and return value from single column

  1. #1
    Registered User
    Join Date
    05-03-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    VLOOKUP to search multiple columns and return value from single column

    Hello.

    I am trying to create an equation that can do a search of multiple columns and return the value from a single column that may be associated to another (for example, if I search columns A, B and C and find the matching value in column C, I would like to only return the associated value in Column A). I have attached an Excel file of example data and the format that it would be in. I understand that the explanation is a little confusing, so please let me know if further clarification is need.


    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP to search multiple columns and return value from single column

    If I understand the need correctly, you want to search for the values in column A of List A and find them ANYWHERE on sheet Master List , and whereever it is found, you want to the get back the value from column A on the Master List.

    If that is correct, then put this array formula in C2 of List A:

    =IF(COUNTIF('Master List'!$A$2:$H$2000, $A2)=0, "", INDEX('Master List'!$A$2:$A$2000, MAX(IF(A2='Master List'!$A$2:$H$2000, ROW('Master List'!$A$2:$H$2000)-1))))

    ...and confirm it by pressing CTRL-SHIFT-ENTER to activate the array, you will see curly braces { } appear around your formula.

    Now copy C2 downward and the ones that are found will give you the Primary Synonym.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: VLOOKUP to search multiple columns and return value from single column

    Ok here's one way, there's probably better ways

    on your List A sheet in B2
    =IF(ISNA(MATCH($A2,'Master List'!A$1:A$1189,0)),0,MATCH($A2,'Master List'!A$1:A$1189,0)+(COLUMN()-1)/10)
    and copy across to column I

    in J2
    =MAX(B2:I2)

    in K2
    =IF(J2=0,"NOT IN MASTER LIST",INDEX('Master List'!A$1:A$1189,INT(J2),1))

    in L2
    =INDEX('Master List'!A1:H1,1,RIGHT(J2,1)*1)

    Now copy B2:L2 down as far as row 184, the last row in List A

    What this does is searches the columns on the Master List for your items on List A and returns a decimal number.
    The first part of the decimal number is the vertical position in the list.
    The second part of the decimal is the Master List column it was found on,. Primary, Ancillary 1, Ancillary 2 etc
    So 4.1 means its associated position in the Primary list is the 4th item down,
    and the .1 means it was found on the Primary list, .2 would be the next column, ie Ancillary 1

    Your results will appear in column K and L
    Column K indicates the associated value in the Primary column
    Column L indicates what list it appeared in.

    Please note in your List A you have values that do not appear in the Master List at all (see Ankle Fusion) and you also have values that appear in several columns of the Master List (see Ankle Arthroscopy)

    Use Ctrl-F (Find) to check this
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    05-03-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: VLOOKUP to search multiple columns and return value from single column

    JBeaucaire,

    Yes, you understand what I was looking for EXACTLY. However, when I entered this formula, it was returning results that didn't seem to match...the value returned from the formula did not match with the value in column A of List A and the corresponding values from the Master List. I have attached a copy of my Excel document after inserting the formula
    Attached Files Attached Files

  5. #5
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: VLOOKUP to search multiple columns and return value from single column

    JBeaucaire's formula works fine.. You need to ensure that you confirm the formula with Ctrl+Shift+Enter as its an array formula

    See JBeaucaire's post
    and confirm it by pressing CTRL-SHIFT-ENTER to activate the array, you will see curly braces { } appear around your formula.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP to search multiple columns and return value from single column

    Did you get the CSE working? (Ctrl-Shift-Enter)

  7. #7
    Registered User
    Join Date
    05-03-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: VLOOKUP to search multiple columns and return value from single column

    JBeaucaire,

    Yes, I was able to make the CSE work. However, when applying the equation to another document, the formula is returning incorrect values...is there anything that needs to be altered when used in a different file? Does it have anything to do with the fact that it is an array formula?

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VLOOKUP to search multiple columns and return value from single column

    Yes, the formula has to be completely edited if the ranges do not suffice. I set it for 2000 rows starting at row2. What does it need to be?

    There is a -1 at the end of the formula that needs to be adjusted for the first row where the formula range evaluates. If the first row in the ranges is row2, then -1 is correct. If the first row in the ranges is row8, then -7 would be the correct edit.

  9. #9
    Registered User
    Join Date
    05-03-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: VLOOKUP to search multiple columns and return value from single column

    That did the trick! Thank you again for all of your help! It is much appreciated

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: VLOOKUP to search multiple columns and return value from single column

    @ BBExcelusr

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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