Index & Match Query

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
764
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hey,

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

CSE
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top