You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
look up two different criteria for result
- Thread starter ganeshm
- Start date
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,
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,
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
Last edited:
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,
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)),"")
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:
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)),"")
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