Multiple Search Criteria Using INDEX/MATCH Functions

  • Thread starter Thread starter Jim Hagan
  • Start date Start date
J

Jim Hagan

Hi all,

I would like to use the INDEX/MATCH functions to return
the column number of data that I'm interested in. My
problem is that I have 2 search criteria that I need to
specify. For instance, with the following sample data
table ...

Parameter Values
---------------------------------
ParamA 2 4 6 2 4 6
ParamB 10 10 10 20 20 20
ParamC 11 22 33 44 55 66

.... I'd like to return the value of ParamC when ParamA = 4
and when ParamB = 20. The syntax for the MATCH function
is,

MATCH(lookup_value,lookup_array,match_type)

In the above table, ParamA and ParamB have repeating
values. The only way to locate the data that I need is by
matching both.

Question: How do I put 2 lookup values into the MATCH
function?

Thanks for any help.

Jim
 
In theory I believe encompassing both MATCH funtions in an AND statemen
might work.

=AND(MATCH(lookup_value,lookup_array,match_type),MATCH(lookup_value,lookup_array,match_type))

At least this is where I would start. I hope that helps.

SheGee
 
Hi Jim,

You can use an Array formula, and in this case, you would have the following
formula, given that all the data you described is in this area A1:G3

=SUM(($B$1:$G$1=4)*($B$2:$G$2=20)*(B3:G3))
Press CTRL+SHIFT+ENTER to enter the formula. You will get 55.

Hope this helps

André
 
Back
Top