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

1. 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)}
Gavin  Reply With Quote

2. Re: Index & Match Query

Hey,

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

CSE  Reply With Quote

3. Re: Index & Match Query Originally Posted by tyija1995 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  Reply With Quote

4. Re: Index & Match Query Originally Posted by gmazza76 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?  Reply With Quote

5. Re: Index & Match Query Originally Posted by tyija1995 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  Reply With Quote

6. Re: Index & Match Query Originally Posted by gmazza76 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?  Reply With Quote

User Tag List

Tags for this Thread

cost, index, match, n21, query  Posting Permissions

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