HLOOKUP

HLOOKUP with multiple criteria (OR/AND logic)

When is the last time you comprehensively looked at HLOOKUP function and its alternative?

To look up data horizontally, many excel users tend to use HLOOKUP but it has its limitations. So what are the alternatives?

In this article, we shall look at?

  1. Horizontal lookup using HLOOKUP, VBA & INDEX & MATCH
  2. How to return multiple items in a Horizontal Lookup
  3. How to return multiple items in a Horizontal Lookup using AND Logic
  4. How to return multiple items in a Horizontal Lookup using OR Logic

►Horizontal lookup using HLOOKUP, VBA & INDEX & MATCH

For a comprehensive look at above check out this previous article

►How to return multiple items in a Horizontal Lookup using AND Logic

All my attempts to use HLOOKUP to return multiple results using (OR/AND) logic failed until 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

►How to return multiple items in a Horizontal Lookup using OR 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

Capture

Leave a Comment:

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