Index & Match Query

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
503
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:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hey,

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

CSE
 

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
503
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
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
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?
 

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
503
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
 

Forum statistics

Threads
1,078,516
Messages
5,340,875
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top