+ Reply to Thread
Results 1 to 4 of 4

vlookup with 2 conditions

  1. #1
    Registered User
    Join Date
    05-06-2013
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    1

    vlookup with 2 conditions

    dear all,

    i have attached one excel file
    need formula help

    i have some data in sheet one and sheet two
    in sheet1
    in a column pan numbers, b column year, c name, d address and e email id

    in sheet2

    pan number, year are there

    what i need is by looking up pan number and year email id should come in sheet2 in c column

    pl. do the n.f.

    regards
    tkrnaidu
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: vlookup with 2 conditions

    Put this in C2 and copy downwards:

    =LOOKUP(2,1/(Sheet1!$A$2:$A$19=Sheet2!A2)*(Sheet1!$B$2:$B$19=Sheet2!B2),Sheet1!E2:E19)
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: vlookup with 2 conditions

    Try this array formula in C2 and copy it down.

    **Array formulas must be confirmed with Ctrl+Shift+Enter key combination.

    =INDEX(Sheet1!$E$2:$E$19,MATCH(A2&B2,Sheet1!$A$2:$A$19&Sheet1!$B$2:$B$19,0))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: vlookup with 2 conditions

    Try this array formula** entered in C2 and copied down:

    =INDEX(Sheet1!E$2:E$19,MATCH(A2,IF(Sheet1!B$2:B$19=B2,Sheet1!A$2:A$19),0))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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 - Country conditions (Multiple conditions)
    By dluhut in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-26-2013, 11:42 AM
  2. VLOOKUP & IF Conditions Together ?
    By VICTOR5 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2012, 02:35 AM
  3. [SOLVED] VLOOKUP but using 2 conditions
    By NickPDC in forum Excel General
    Replies: 6
    Last Post: 03-26-2012, 03:54 AM
  4. VLOOKUP with 2 conditions
    By RSantos in forum Excel General
    Replies: 5
    Last Post: 06-26-2006, 01:15 PM
  5. Vlookup using 2 conditions
    By Jambruins in forum Excel General
    Replies: 3
    Last Post: 11-15-2005, 10:20 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