When was the last time you comprehensively looked at the 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

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 MULTIPLE ITEMS

=INDEX(Staff,
   SMALL(
     IF(
       (Dates=$J$3)*(Attendance=$J$4),COLUMN(Staff)-1,""),
       ROW(A1)))

How it Works

►First, create named ranges for easy referencing

  • Staff=B2:E7
  • Dates=A3: A14
  • Attendance=B3:E14
  • Column(staff)-1= standard array of column numbers

(Dates=$J$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=$J$4) checks for P in the attendance data 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=$J$3)*(Attendance=$J$4) the two Boolean arrays are converted into their numeric equivalent of 1 & o during multiplication 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 the 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=$J$3)*(Attendance=$J$4),COLUMN(Staff)-1,””) IF function returns a columns number where the criteria is TRUE else blank (“”)

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

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

►The 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=$J$7)*((Attendance=$J$8)+(Attendance=$J$9)))=1,COLUMN(Staff)-1,"")
,ROW(A1)))

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 were met), 1 (where the dates criterion was met and either one of the other 2 criteria were met)

{0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,0,0,0;0,1
,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,0}

(Dates=$L$3)*((Attendance=$L$4)+(Attendance=$L$5))=1 checks occurrences of 1 in the above array and returns a Boolean array.

►IF function returns a columns numbers where criteria are TRUE

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

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

Conclusion:

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

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

DOWNLOAD HLOOKUP WITH DUPLICATES WORKSHEET

RELATED ARTICLES

HLOOKUP, INDEX & MATCH OR VBA

REVERSE LOOKUP WITH DUPLICATES IN EXCEL

RECOMMENDED READING

Ctrl+Shift+Enter Array Formula Book