+ Reply to Thread
Results 1 to 8 of 8

Vlookup off 2 criteria

  1. 07-02-2015, 03:29 PM #1
    jchohan83
    jchohan83 is offline
    Registered User
    Join Date
    08-10-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Vlookup off 2 criteria

    I am trying to use a vlookup off one sheet to another but based on 2 criteria:

    ex. i have 10 columns on a summary sheet and want to reference cell D2 and G2 to return off a main data sheet. what would be the formula to write? the reason for 2 criteria is cell D2 takes it down one level and G2 will get me to the answer i need.


    If the main data sheet is all sorts of data on apples and has red, green, blue, purple, yellow, etc (criteria 1) in one column and apple types washington, gala, fuji, ect. (criteria 2) in another column....... i want it to return the price which what the lookup is to return.
    Attached Files Attached Files
    Last edited by jchohan83; 07-02-2015 at 07:13 PM. Reason: Solved!
    Reply With QuoteRegister To Reply
  2. 07-02-2015, 04:04 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Vlookup off 2 criteria

    Probably the most efficient way to do this is to insert a new Column A on your Data Sheet and copy all the way down =B1&C1
    You can then hide column A
    Now on your summary sheet In C2
    =VLOOKUP(A2&B2, Data!$A$2:$D$168,4,FALSE)
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 07-02-2015, 04:07 PM #3
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,192

    Re: Vlookup off 2 criteria

    one way

    =VLOOKUP(M1&N1,CHOOSE({1,2},A1:A3&B1:B3,C1:C3),2,0)

    M1 / N1 contain your criteria e,g Red/Washington A&B contain the data (Apple colour/type), C is your cost

    Enter with Ctrl+shift+enter

    The columns need not be consecutive i.e you could have A, C ,E instead of A,B,C
    Reply With QuoteRegister To Reply
  4. 07-02-2015, 04:20 PM #4
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: Vlookup off 2 criteria

    Hey John, would you mind explaining what the CHOOSE({1,2} does?

    jchohan83, sorry for hijacking your thread, I hope I don't create a mess.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  5. 07-02-2015, 04:57 PM #5
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,192

    Re: Vlookup off 2 criteria

    Simply selects the A & B ranges for the concatenation. You will note that the column index is 2 referring to range in C.

    This a "trick" I picked up from the Internet.
    Reply With QuoteRegister To Reply
  6. 07-02-2015, 07:10 PM #6
    jchohan83
    jchohan83 is offline
    Registered User
    Join Date
    08-10-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Vlookup off 2 criteria

    I did not think of joining the 2 cells and creating a new unique identifier, thank you for the recommendation!!
    Reply With QuoteRegister To Reply
  7. 07-02-2015, 07:16 PM #7
    jchohan83
    jchohan83 is offline
    Registered User
    Join Date
    08-10-2011
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Vlookup off 2 criteria

    Thanks for the reply! I will have to play around with this one a little, Ctrl+Shift+Enter is to end the formula?
    Reply With QuoteRegister To Reply
  8. 07-03-2015, 03:49 AM #8
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,192

    Re: Vlookup off 2 criteria

    Yes.= Enter my VLOOKUP with Ctrl+Shift+Enter
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Large multi criteria / Vlookup/Choose Multi criteria
    By deanusa in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2014, 01:48 AM
  2. vlookup with 2 criteria
    By khurramfarooqpk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2013, 07:48 AM
  3. 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
  4. Vlookup with two criteria
    By mkvassh in forum Excel General
    Replies: 7
    Last Post: 10-22-2009, 08:21 AM
  5. [SOLVED] two criteria in a vlookup
    By CMAC in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-04-2005, 02:06 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1