INDEX/MATCH Function

[Bob]

I tried the INDEX function however I received an #N/A
error. Not sure if I'm typing it in correctly or not.

My table columns are J2:L4 and the data in the spreadsheet
are in columns M&P and I typed the function in column Q.

Here is what I typed:
=INDEX(Projects!$L$2:$L$4,MATCH(J2&K2,Projects!
$J$2:$J$4&Projects!$K$2:$K$4,0))

Thanks.

Bob

 

[Dave R.]

Bob, try something like this;


=MATCH(J10&K10,CONCATENATE(J2:J4,K2:K4),0)

and ARRAY enter it (control shift enter instead of just enter).

 

[Dave R.]

In fact I think it will work just using control shift enter with your
current formula too, so no need to use concatenate.

 

[Don Guillett]

Maybe this will help.

INDEX(reference,row_num,column_num,area_num)

Reference is a reference to one or more cell ranges.

a.. If you are entering a nonadjacent range for the reference, enclose
reference in parentheses.

b.. If each area in reference contains only one row or column, the row_num
or column_num argument, respectively, is optional. For example, for a single
row reference, use INDEX(reference,,column_num).

Row_num is the number of the row in reference from which to return a
reference.

Column_num is the number of the column in reference from which to return a
reference.

Area_num selects a range in reference from which to return the
intersection of row_num and column_num. The first area selected or entered
is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX
uses area 1.

a.. For example, if reference describes the cells (A1:B4,D1:E4,G1:H4),
then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and
area_num 3 is the range G1:H4.