Results 1 to 6 of 6

Thread: Index & Match Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Location
    Manchester
    Posts
    503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index & Match Query

    Good afternoon,

    I am trying to complete an index & match query to return a title from column "H" based on the following criteria

    M20 - Book type (hardback or Paperback)
    M21 - cost to me
    N21 - cost to customer

    M20 And column "H" are set as text values and M21 & N21 are as numbers

    Both are done as nested formulas with Ctrl+shift+ enter

    With the formulas below I get a #REF ! issue

    Code:
    =INDEX(C:C,MATCH(M21,H:H,0),MATCH(N21,I:I,0),MATCH(M20,A:A,0))
    with the below I get a #VALUE error
    Code:
    {=INDEX(C:C,MATCH(M21,H:H)*(N21,I:I)*(M20,A:A),0)}
    thanks in advance
    Gavin
    Last edited by gmazza76; Aug 30th, 2019 at 09:30 AM.

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Index & Match Query

    Hey,

    Try:
    =INDEX(C:C,MATCH(1,(M21=H:H)*(N21=I:I)*(M20=A:A),0))

    CSE
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Location
    Manchester
    Posts
    503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index & Match Query

    Quote Originally Posted by tyija1995 View Post
    Hey,

    Try:
    =INDEX(C:C,MATCH(1,(M21=H:H)*(N21=I:I)*(M20=A:A),0))

    CSE
    THANKS, I believe I missed the "1" out but what does this specify in the formula.

    Also do you know how I can get the formula to work in multiple rows instead of F2 it and doing ctrl+shift+enter

  4. #4
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Index & Match Query

    Quote Originally Posted by gmazza76 View Post
    THANKS, I believe I missed the "1" out but what does this specify in the formula.

    Also do you know how I can get the formula to work in multiple rows instead of F2 it and doing ctrl+shift+enter
    The 1 is the value you are finding with the multiple conditions, so you are picking up the first instance where M21=H:H AND N21=I:I AND M20=A:A.

    When you say to work in multiple rows, are you wanting the M20, M21 and N21 values to be FIXED, so when you drag formula down they stay as is? Or are they relative?
    √-1 2³ ∑ π
    …And it was delicious!

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Location
    Manchester
    Posts
    503
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index & Match Query

    Quote Originally Posted by tyija1995 View Post
    The 1 is the value you are finding with the multiple conditions, so you are picking up the first instance where M21=H:H AND N21=I:I AND M20=A:A.

    When you say to work in multiple rows, are you wanting the M20, M21 and N21 values to be FIXED, so when you drag formula down they stay as is? Or are they relative?

    Dragged down if possible, but also when I change the data in the fields that the calculation works from

  6. #6
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Index & Match Query

    Quote Originally Posted by gmazza76 View Post
    Dragged down if possible, but also when I change the data in the fields that the calculation works from
    OK what cell are you putting the starting formula in to?
    √-1 2³ ∑ π
    …And it was delicious!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •