• 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

  • grade sheet.xlsx
    12.4 KB · Views: 10
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))
 
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
 
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.
 
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

  • grade sheet (BY).xlsx
    13.1 KB · Views: 6
Using Power Query, I created two parameter lookups. When you select the Refresh Button, it filters your table
 

Attachments

  • grade sheet.xlsm
    40.2 KB · Views: 1
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
 
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.
 
Back
Top