HLOOKUP

HLOOKUP with multiple criteria (OR/AND logic)

For some time now, I have looked for a way to look up horizontally duplicate data using (OR/AND) logic. All the attempts to use HLOOKUP to return multiple results using (OR/AND) logic failed.

Then I discovered a combination of  INDEX, SMALL, IF and ROW functions that worked like magic.

Suppose you have below attendance register and you want to look up and return all PRESENT staff on 01-07-2016.

HLOOKUP OR

{=INDEX(Staff,SMALL(IF(((Dates=$I$3)*(Attendance=$I$4))=1,{1,2,3,4},""),ROW(A1)))}

HLOOKUP OR

How it Works

►First, create named ranges for easy referencing

  • Staff=B2:E7
  • Dates=A3: A14
  • Attendance=B3:E14

(Dates=$I$3) checks for 01-07-2017 among the dates and returns an array of TRUE/FALSE.

{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

(Attendance=$I$4) checks for P in the attendance date and returns an array of TRUE/FALSE

{TRUE,FALSE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE;TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,FALSE,FALSE;TRUE,TRUE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE;FALSE,FALSE,TRUE,TRUE;TRUE,TRUE,TRUE,TRUE}

► (Dates=$I$3)*(Attendance=$I$4) during multiplication, the two Boolean arrays are converted into their numeric equivalent of 1 & o and then using the techniques of conjunction truth table, below numeric array is produced.

{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;1,0,0,1;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0}

((Dates=$I$3)*(Attendance=$I$4))=1 checks occurrences of 1 in above array and returns a Boolean array below

{FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}

IF(((Dates=$I$3)*(Attendance=$I$4))=1,{1,2,3,4},””) on the multiple occurrences of TRUE, IF function returns a a columns number from this array ({1,2,3,4}) as shown below

{=INDEX(Staff,SMALL({"","","","";"","","","";"","","","";"","","","";"","","","";1,"","",4;"","","","";"","","","";"","","","";"","","","";"","","","";"","","",""},ROW(A1)))}

SMALL function returns the column numbers one at a time using the ROW(A1) number as its K

INDEX function returns the staff name given the column number by the small function.

NB: 

  • This was an example of looking up horizontally using AND logic.
  • You can use an asterisk (*) to replace AND logic

Now, Suppose you want to look up and return the staff who were Either on Off (WO) or Leave (LV) on 01-07-2017

{=INDEX(Staff,SMALL(IF(((Dates=$L$3)+(Attendance=$L$4)+(Attendance=$L$5))=2,{1,2,3,4},""),ROW(D1)))}

FINAL

How it Works

► This time we use a plus (+) to replace OR logic

=IF(OR(Criteria_range1=criteria1,Criteria_range2=criteria2) Does NOT return an array. Therefore, to go around this limitation use plus (+) sign

((Dates=$L$3)+(Attendance=$L$4)+(Attendance=$L$5)) Returns an array of 0 (where no criteria was met), 1 (where at least 1 criterion was met) and 2(where at least 2 criteria were met)

{0,1,1,0;0,0,0,0;0,0,0,0;1,1,0,0;0,0,0,0;1,2,2,1;0,0,0,0;0,0,1,1;0,0,0,0;0,0,0,0;1,1,0,0;0,0,0,0}

((Dates=$L$3)+(Attendance=$L$4)+(Attendance=$L$5))=2 checks occurrences of 2 in above array and returns a Boolean array below

{FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}

IF(((Dates=$L$3)+(Attendance=$L$4)+(Attendance=$L$5))=2,{1,2,3,4},””) on the multiple occurrences of TRUE, IF function returns a a columns number from this array ({1,2,3,4}) as shown below

{"","","","";"","","","";"","","","";"","","","";"","","","";"",2,3,"";"","","","";"","","","";"","","","";"","","","";"","","","";"","","",""}

SMALL function returns the column numbers one at a time using the ROW(A1) number as its K

INDEX function returns the staff name given the column number by the small function.

Conclusion:

This is all you need to solve horizontal look up issues that require;

  • Checking on multiple criteria using OR logic or AND logic
  • Has Duplicates and thus requires multiple returns

DOWNLOAD WORKSHEET

RELATED ARTICLES

HLOOKUP, INDEX & MATCH OR VBA

REVERSE LOOKUP WITH DUPLICATES IN EXCEL

RECOMMENDED READING

Ctrl+Shift+Enter Array Formula Book

Leave a Comment:

Your email address will not be published. Required fields are marked *