+ Reply to Thread
Results 1 to 6 of 6

Vlookup with multiple criteria

  1. #1
    Registered User
    Join Date
    11-27-2014
    Location
    Alberta,Canada
    MS-Off Ver
    2007
    Posts
    22

    Vlookup with multiple criteria

    Hi there...I have two worksheets...both downloaded from different systems. I need to be able to look up the EMPID AND the question from worksheet 1 to pull the answers from worksheet 2. I don't want to have to concatenate columns Please help!

    Worksheet 1
    A B C D
    1 EMPID What have I done What haven't I done What Can I do
    2 10777
    3 10778

    Worksheet2
    A B C
    1 10777 What have I done Not alot
    2 10777 What haven't I done Quite a bit
    3 10777 What can I do As little as possible
    4 10778 What have I done Been very Busy
    5 10778 What haven't I done Done everything
    etc (there are about 10000 lines)
    Attached Files Attached Files
    Last edited by DeborahClark; 03-31-2020 at 04:10 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup with multiple criteria

    Wouldn't a Pivot Table be a simpler solution. See attached.
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Vlookup with multiple criteria

    Sheet1

    B2=IFERROR(INDEX(Sheet2!$C$2:$C$1000,SMALL(IF(Sheet2!$A$2:$A$1000=Sheet1!$A2,IF(Sheet2!$B$2:$B$1000=Sheet1!B$1,ROW(Sheet2!$C$2:$C$1000)-ROW(Sheet2!$C$2)+1)),COLUMNS(Sheet1!$B$1:Sheet1!$B1))),"")

    Control+shift+enter

    copy across and down
    Last edited by CARACALLA; 03-31-2020 at 04:51 PM.

  4. #4
    Registered User
    Join Date
    11-27-2014
    Location
    Alberta,Canada
    MS-Off Ver
    2007
    Posts
    22

    Re: Vlookup with multiple criteria

    Hi Richard,

    No....There is actually a lot more data on that first sheet.....and we have to sort it in a very specific way. I can't download your sheet as it is opening as a .php

  5. #5
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Vlookup with multiple criteria

    at B2 sheet 1
    =IF(SUMPRODUCT(($A2=Sheet2!$A$2:$A$6)*(B$1=Sheet2!$B$2:$B$6)*(ROW(Sheet2!$C$2:$C$6)-1))=0,"",INDEX(Answer,SUMPRODUCT(($A2=Sheet2!$A$2:$A$6)*(B$1=Sheet2!$B$2:$B$6)*(ROW(Sheet2!$C$2:$C$6)-1))))

    copy cross and down

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Vlookup with multiple criteria

    A PT can handle large volumes of data and can be sorted (although I admit I don't know what sorting in a 'very specific way' actually means).

    I can't see why you can't downoad and open the workbook. I've just downloaded it from EF to my local drive and it opens as normal Excel workbook.
    It sounds like your system is changing it to a .php extension

+ 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] Vlookup for multiple column index number and Multiple criteria
    By Marshall008 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-13-2019, 08:36 PM
  2. Replies: 5
    Last Post: 05-27-2019, 03:46 PM
  3. [SOLVED] Vlookup multiple criteria returning multiple values
    By ykobure in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-16-2015, 07:28 AM
  4. [SOLVED] iserror and vlookup with multiple criteria from multiple tables along with concatenation.
    By marcusduton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-14-2015, 02:16 AM
  5. Vlookup (or index/match) with multiple criteria over multiple sheets
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 01:56 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Vlookup + multiple criteria + multiple data returned
    By stonesy in forum Excel General
    Replies: 6
    Last Post: 02-17-2010, 08:53 AM

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