• 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.

look up two different criteria for result

G

ganeshm

Member
hi all,

I am trying to look up two different criteria in order to get result in the attached excel file, but i was successful with only one criteria.

Need an idea as how to execute the formula for the second criteria too. File is attached for reference.

Regards,
ganeshm
 

Attachments

  • For Test.xlsx
    18.6 KB · Views: 9
Thank you so much. Just a quick question regarding the specific selection of function, What if i got to select only "Manager", rather than SENIOR category.

Regards,
 
Last edited:
ganeshm said:
Thank you so much. Just a quick question regarding the specific selection of function, What if i got to select only "Manager", rather than SENIOR category.

Regards,
Click to expand...

If Source data got more than 1 result, use this :

{=IFERROR(INDEX('Master File'!$C$2:$I$22,SMALL(IF(($B$1='Master File'!$G$2:$G$22)*($D$1='Master File'!$H$2:$H$22),ROW('Master File'!$C$2:$C$22)-ROW('Master File'!$C$2)+1),ROWS(B$1:B1)),MATCH(B$2,'Master File'!$C$1:$I$1,0)),"")}

If Source data got only 1 result, use this :

{=IFERROR(INDEX('Master File'!$C$2:$I$22,MATCH(1,($B$1='Master File'!$G$2:$G$22)*($D$1='Master File'!$H$2:$H$22),0),MATCH(B$2,'Master File'!$C$1:$I$1,0)),"")}

Regards
 
hi..

Further to the above mentioned example, i tried and the following formula (with array) works fine, giving desired result as per the ranks:

{=IFERROR(INDEX('Master File'!$A$2:$G$22,SMALL(IF(($B$1='Master File'!$E$2:$E$22)*ISNUMBER(SEARCH($D$1,'Master File'!$F$2:$F$22)),ROW('Master File'!$A$2:$A$22)-ROW('Master File'!$A$2)+1),ROWS(F$1:F1)),MATCH(F$2,'Master File'!$A$1:$G$1,0)),"")}

Also, i am trying to solve another criteria - For e.g. if i try dept.; i get result, what if, if i try only position. I am not getting the result, should i use OR function with index.

Regards,
 

Attachments

  • 2CriteriaLookupTest.xlsx
    17.9 KB · Views: 9
Last edited:
ganeshm said:
hi..

Further to the above mentioned example, i tried and the following formula (with array) works fine, giving desired result as per the ranks:

{=IFERROR(INDEX('Master File'!$A$2:$G$22,SMALL(IF(($B$1='Master File'!$E$2:$E$22)*ISNUMBER(SEARCH($D$1,'Master File'!$F$2:$F$22)),ROW('Master File'!$A$2:$A$22)-ROW('Master File'!$A$2)+1),ROWS(F$1:F1)),MATCH(F$2,'Master File'!$A$1:$G$1,0)),"")}

Also, i am trying to solve another criteria - For e.g. if i try dept.; i get result, what if, if i try only position. I am not getting the result, should i use OR function with index.

Regards,
Click to expand...

Try.........

Add a &"" inside your I2 formula as per highlighted red and copy down :

=IFERROR(INDEX($F$3:$F$23,MATCH(0,INDEX(COUNTIF($I$1:I1,$F$3:$F$23&""),0,0),0)),"")

Regards
Bosco
 
thank you once again, it works in I2 as mentioned above.
Conditions in cell:

1. Type dept. in B1, it gives result in ROW 3.
2. Type dept. in B1 & position in D1, it gives result in ROW 3.

3. Type only position in D1, i don't get result in row 3. For this, i used to following formula;

=IFERROR(INDEX('Master File'!$A$2:$G$200,SMALL(IF(($D$1='Master File'!$F$2:$F$200)*ISNUMBER(SEARCH($D$1,'Master File'!$F$2:$F$200)),ROW('Master File'!$A$2:$A$200)-ROW('Master File'!$A$2)+1),ROWS(F$1:F1)),MATCH(F$2,'Master File'!$A$1:$G$1,0)),"")
 
Last edited:
ganeshm said:
thank you once again, it works in I2 as mentioned above.
Conditions in cell:

1. Type dept. in B1, it gives result in ROW 3.
2. Type dept. in B1 & position in D1, it gives result in ROW 3.

3. Type only position in D1, i don't get result in row 3. For this, i used to following formula;

=IFERROR(INDEX('Master File'!$A$2:$G$200,SMALL(IF(($D$1='Master File'!$F$2:$F$200)*ISNUMBER(SEARCH($D$1,'Master File'!$F$2:$F$200)),ROW('Master File'!$A$2:$A$200)-ROW('Master File'!$A$2)+1),ROWS(F$1:F1)),MATCH(F$2,'Master File'!$A$1:$G$1,0)),"")
Click to expand...

1] The formula is a 2 criteria Lookup, so you must always have 2 criteria in B1 and D1.

The 1st criteria is : ($B$1='Master File'!$E$2:$E$22) --> it is a exact matching criteria

The 2nd criteria is : ISNUMBER(SEARCH($D$1,'Master File'!$F$2:$F$22)) --> it is a approx. matching criteria

2] In order always get data inside the criteria cells, it is generally use validation dropdown list.

3] You must decide what do you want prior to design the formula in suit with the Output Table.

Regards
Bosco
 
You must log in or register to reply here.
Back
Top