+ Reply to Thread
Results 1 to 8 of 8

vlookup or indexmatch 3 criteria vert and hor

  1. #1
    Forum Contributor
    Join Date
    07-15-2015
    Location
    germany
    MS-Off Ver
    365
    Posts
    100

    vlookup or indexmatch 3 criteria vert and hor

    Dear,

    I would like to display the content of one cell. the lookup is dependent on multiple (in added example 2 variables in colums and 1 in a row)

    Can any one help with a this without using a helper column or row.


    regards

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: vlookup or indexmatch 3 criteria vert and hor

    hi jaapaap. try:
    =SUMIFS(INDEX(C2:Y7,0,MATCH(E19,C1:Y1)),A2:A7,C19,B2:B7,D19)

    or an array formula:
    =INDEX(C2:Y7,MATCH(C19&"_"&D19,A2:A7&"_"&B2:B7,0),MATCH(E19,C1:Y1,0))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    07-15-2015
    Location
    germany
    MS-Off Ver
    365
    Posts
    100

    Re: vlookup or indexmatch 3 criteria vert and hor

    thanks a lot. This works.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: vlookup or indexmatch 3 criteria vert and hor

    Or with a pivot table (after re-arange the data).

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Contributor
    Join Date
    07-15-2015
    Location
    germany
    MS-Off Ver
    365
    Posts
    100

    Re: vlookup or indexmatch 3 criteria vert and hor

    @benishiryo

    is it possible that te function does not work when you format as table. See attachment.

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: vlookup or indexmatch 3 criteria vert and hor

    @jaapaap:
    Table doesn't accept numbers as the heading, so it converts them into texts. a simple addition in red will suffice, to find the number as a text:
    =INDEX(C2:Y7,MATCH(C19&"_"&D19,A2:A7&"_"&B2:B7,0),MATCH(E19&"",C1:Y1,0))

  7. #7
    Forum Contributor
    Join Date
    07-15-2015
    Location
    germany
    MS-Off Ver
    365
    Posts
    100

    Re: vlookup or indexmatch 3 criteria vert and hor

    ok thanks, did not knew this

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: vlookup or indexmatch 3 criteria vert and hor

    @jaapaap

    you did not reply on my solution in #4.

+ 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. Getting cell content based on multiple columns of data (Vlookup? IndexMatch?)
    By markhooten in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-11-2017, 04:48 PM
  2. Convert VLookup to INDEXMATCH help
    By sentinel618 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-14-2017, 07:44 AM
  3. [SOLVED] Vlookup / IndexMatch to return not just the 1st instance of an occurance
    By DaveBre in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-21-2014, 05:18 PM
  4. excel vert & hor lookup or perhaps index
    By swwell in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 02-16-2014, 08:41 PM
  5. vlookup or indexmatch on closest name match?
    By jw01 in forum Excel General
    Replies: 4
    Last Post: 05-14-2012, 03:38 PM
  6. Help on if/and/vlookup?indexmatch? Please help
    By Heerah in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-09-2012, 12:11 AM
  7. IndexMatch w/3 (or more) criteria
    By TimR in forum Excel General
    Replies: 17
    Last Post: 08-02-2007, 08:12 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