+ Reply to Thread
Results 1 to 8 of 8

VLookup on multiple columns

  1. #1
    Registered User
    Join Date
    02-23-2015
    Location
    Manama
    MS-Off Ver
    2007
    Posts
    1

    VLookup on multiple columns

    I would like to know how to use VLOOKUP or any other function that would search for a " text or numeric value" in Multiple columns and returns a value if matched. For instance, I have Col A = [Phone 1], Col B = [Phone 2], Col C = [Phone 3] and finally Col D = [contact name]. So my objective is to have Col F return the respective [contact name] based on the input in Col E that would search for the matching string in the entire Columns A & B & C . I have a limited knowledge on VLOOKUP function but since there are multiple columns to search, I don't know if VLOOKUP works here and if not what alternative functions can be used to achieve this?

    File attached
    Attached Files Attached Files
    Last edited by sajohn; 03-08-2015 at 08:16 AM. Reason: For more clarity

  2. #2
    Registered User
    Join Date
    04-18-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLookup on multiple columns

    Not sure if this is the right answer but shouldn't an hlookup work?
    Vlookup is restricted to one column multiple rows.
    Hlookup works with one row, multiple columns.

    Just a suggestion.
    Hopefully this works.
    Good luck.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,990

    Re: VLookup on multiple columns

    will this be done on a row-by-row basis, or do you have a table full of number's/names, and you want to find a number anywhere in that table?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    45,990

    Re: VLookup on multiple columns

    Assuming you have a long list of names.numbers and want to find 1 of them...
    =INDEX($D$2:$D$6,IFERROR(MATCH(E2,$A$2:$A$6,0),IFERROR(MATCH(E2,$B$2:$B$6,0),MATCH(E2,$C$2:$C$6,0))))

  5. #5
    Registered User
    Join Date
    04-18-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLookup on multiple columns

    We do not need to use either Vlookup or Hlookup after all - just simple IF(OR) will suffice.

    Based on your explanation and data example provided, these are my assumptions:
    - Column D (Contact name) may have up to three phone numbers listed in columns A through C
    - you want to be able to enter any phone numbers in column E and if it matches any of the phone numbers in columns A - C, you want the Contact Name to appear in column F

    Copy this formula in column F and auto fill formula down to the last row of your data
    =IF(OR(E:E=A:A,E:E=B:B,E:E=C:C),D:D,"")

    Please see attached for example.
    Also if formula returns no contact name, you can replace the "" (which results in blank) to whatever you want - example "No Contact" or "NIL" instead of leaving it blank.

    Let me know if this works for you.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    998

    Re: VLookup on multiple columns

    FDibbins' formula works perfectly. This would also work. Sumproduct can look at the whole range of phone numbers and find the match. Row will tell you which row the match is in. You just have to -1 on the row because the data starts in the 2nd row.

    I only post this because it could be expanded to look at many more columns.

    Please Login or Register  to view this content.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

  7. #7
    Registered User
    Join Date
    04-18-2013
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLookup on multiple columns

    Glad it worked for you.
    I too learned something new today. : )

  8. #8
    Registered User
    Join Date
    12-31-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: VLookup on multiple columns

    will the =indirect function work in this case?

+ 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