• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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.
Adding the ¬ character means they would now be different: Grade 1¬11 and Grade 11¬1.
 

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)

72242
 

Attachments

Peter Bartholomew

Well-Known Member
My formula is simply
= XLOOKUP(required, gradeYrs, salary)
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) )
 
gradeYrs:
= LET(
  gradeIdx, 1+FLOOR(SEQUENCE(22)-1,11),
  grade, INDEX( grades, gradeIdx),
  recordHdr, grade & ", Yrs." & Yrs.,
  recordHdr )
 
required:
= @currentGrade & ", Yrs." & @CurrentYrs
72251
 

ajnabidst

New Member
Thank you all of you. It works well and am so grateful to have excellent team of experts here.
Once again, thank you so much.
 
Top