# lookup 2 different criteria and return value

#### ajnabidst

##### New Member
Hi all,

I am try to look up two criteria and find the req. value in the corresponding cell.
But instead of giving corres. value, it is selecting the max. value of the cell. Am not sure where have i given wrong cell reference.
Kindly assist.

regards,
ganesh

#### Attachments

• 12.4 KB Views: 10

#### p45cal

##### Well-Known Member
Try in E2:
=INDEX(MASTER!\$D\$3:\$D\$24,MATCH(B2&C2,MASTER!\$L\$3:\$L\$24 & MASTER!\$C\$3:\$C\$24,0))
or more robustly:
=INDEX(MASTER!\$D\$3:\$D\$24,MATCH(B2&"¬"&C2,MASTER!\$L\$3:\$L\$24 & "¬" & MASTER!\$C\$3:\$C\$24,0))

#### ajnabidst

##### New Member
Try in E2:
=INDEX(MASTER!\$D\$3:\$D\$24,MATCH(B2&C2,MASTER!\$L\$3:\$L\$24 & MASTER!\$C\$3:\$C\$24,0))
or more robustly:
=INDEX(MASTER!\$D\$3:\$D\$24,MATCH(B2&"¬"&C2,MASTER!\$L\$3:\$L\$24 & "¬" & MASTER!\$C\$3:\$C\$24,0))
Thanks for the quick reply. It works.
May i ask what does this "¬"mean in excel

#### p45cal

##### Well-Known Member
It means nothing; it's just a rarely used character.
Your sample data doesn't need it, but it's not beyond the realms of possibility that you have a Grade 1 and a Grade 11 as well as Years 1 and 11.
With the first formula both Grade 1 Year 11 and Grade 11 Year 1 would concatenate to Grade 111 so there would be confusion as to which it was.

#### bosco_yip

##### Excel Ninja
In order to have a single copy across down formula, please arrange the 3 sets of "Steps", "Yrs" and "sal" sub-column placed in the same order.

Then,

In "Calc" sheet cell E2, formula copied across and down :

=SUMIFS(INDEX(MASTER!\$D:\$J,0,MATCH(E\$1,MASTER!\$B\$1:\$J\$1,0)),MASTER!\$L:\$L,\$B2,INDEX(MASTER!\$C:\$J,0,MATCH(E\$1,MASTER!\$B\$1:\$J\$1,0)),\$C2)

#### Attachments

• 13.1 KB Views: 5

#### AlanSidman

##### Well-Known Member
Using Power Query, I created two parameter lookups. When you select the Refresh Button, it filters your table

#### Attachments

• 40.2 KB Views: 1

#### Peter Bartholomew

##### Well-Known Member
My formula is simply
which returns a complete row of 3 values. The catch is that to create 'simplicity' I have had to rebuild the arrays in memory
Code:
``````salary:
= LET(
SIMidx, 3*{1,2,3},
stepIdx, SEQUENCE(22),
INDEX(data, stepIdx, SIMidx) )

= LET(