+ Reply to Thread
Results 1 to 6 of 6

Lookup multiple columns

  1. #1
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459

    Lookup multiple columns

    I have a main telephone number lets say in Column A then in columns B to L I have all I have more telephone numbers, then finally in column M i have a Priority ID.

    What i want to do is lookup the telephone number in column A and give me the Priority ID in column M if the telephone number is in either columns B,C,D,E,F,G,H,I,J,K,L

    I can do a VLookup but this will only give me a result if the telephone number matches in the first column of the lookup, in this case column B.

    Kind regards

    David

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,094

    Re: Lookup multiple columns

    Try this if you need the same phone no in all: =IF(COUNTIF(B2:L2,A2)>=11,M2,"One doesn't match")
    If only one other match is needed: =IF(COUNTIF(B2:L2,A2)>=2,M2,"No match")
    Click the * to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    628

    Re: Lookup multiple columns

    (Sorry about my English)


    If your data is A1:M11
    Formula maybe
    =INDEX(M1:M11,AGGREGATE(15,6,ROW(A1:L11)/(1/(A1:L11="Tel_number")),1))

    Or if you put a tel-number in N1 (instead direct in formula)
    =INDEX(M1:M11,AGGREGATE(15,6,ROW(A1:L11)/(1/(A1:L11=N1)),1))

    Regards.
    My English is very poor, so please be patient >_<"

  4. #4
    Forum Contributor
    Join Date
    08-24-2006
    Location
    Sevenoaks, Kent
    MS-Off Ver
    Office 365 ProPlus
    Posts
    459

    Re: Lookup multiple columns

    Thanks, but this will only work if the matching data is on the same row, I want to lookup B1:L2000 and give me the result of column M where a match is found.

    Sorry, I should have been clearer.

    David

  5. #5
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016
    Posts
    628

    Re: Lookup multiple columns

    Did you mean the telephone number can be duplicated in others row? (So many of M will be reported)

    Regards.

  6. #6
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Lookup multiple columns

    Hi

    I suppose you want this.
    Use this formula to get the ID in M3:M15 from the phone numbers in the table A3:L15, where D20 is the search phone number.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the file for clarification.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup multiple criteria in multiple columns in multiple rows; return true if exists
    By ufdlim in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-07-2019, 02:03 PM
  2. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  3. [SOLVED] VLookup - Single value lookup returning multiple records into multiple columns
    By kllovin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-07-2014, 05:14 AM
  4. Replies: 0
    Last Post: 10-16-2013, 12:42 PM
  5. [SOLVED] Multiple lookup adding columns, then multiplying, then lookup, add, and subtract
    By mamig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-08-2013, 12:55 AM
  6. Lookup Data in One Column, Find Multiple Entries in Next, Copy to Multiple Columns
    By nzxt1234 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 01:17 AM
  7. Lookup adjacent column to multiple lookup columns.
    By JAMES4228 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-13-2009, 03:19 PM

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