# HLOOKUP, INDEX & MATCH OR VBA

Having done a series on Verticle lookup, it is only fair to touch on the Horizontal lookup.

HLOOKUP function is the most popular function for this kind of lookup but it has a number of limitations and thus one may need to know a few other ways you can carry out a Horizontal lookup.

For example, given below data of suppliers, items supplied and prices for each supplier, Can you extract the **least expensive supplier(s)?**

Most users would jump to HLOOKUP for this task. but it is the least effective based on its limitations. I will start with the most effective–VBA

# VBA

We are going to use a simple code I learned from the Videos I recommended in REVERSE LOOKUP USING VBA article.

It just requires a little tweaking and it will be able to do a horizontal lookup and return duplicates

Function HL(Price As Range, LookupPrices As Range, k As Integer) CustomerNames = LookupPrices.Rows(1).Row - k HL = "" For Each cell In LookupPrices If cell.Value = Application.WorksheetFunction.Min(Price.Value) Then HL = HL & Cells(CustomerNames, cell.Column).Value & " ; " End If Next cell End Function

## Results

## Here is the Breakdown:

### ►Create a Function that accepts 3 parameters

`Function HL(Price As Range, LookupPrices As Range, k As Integer)`

- What to lookup (Price As Range)
- Where to Lookup (LookupPrices As Range)
- Offset factor from Customer names row (k)

### ►Declare what you want to return:

**Customer Names**which are k row(s) above the Lookup Prices

`CustomerNames = LookupPrices.Rows(1).Row - k`

**NB:** You have to create a helper column to store this k Factor which is calculated by a simple formula (=ROW()-ROW($I$2))

### ►Declare a variable that will hold your results.

HL = ""

**NB. The variable should be in the same Name as your Function**

### ►Declare a variable that the function will loop through

For Each cell In LookupPrices If cell.Value = Application.WorksheetFunction.Min(Price.Value)

For every **cell** that contains the Minimum Price in the Range return the customer name & separate the result(s) with (** ;** )

**NB: Learn more about Calling Worksheet Functions In VBA**

HL = HL & Cells(CustomerNames, cell.Column).Value & " ; "

# INDEX & MATCH

Other than VBA, I would highly recommend using a combination of INDEX & MATCH. Apply below formula per every item

=INDEX($C$2:$H$2, MATCH(MIN(C3:H3),C3:H3,0))

### How It Works;

►MIN(C3:H3) Returns the minimum price for the item in row 3

MIN(C3:H3)=600

►MATCH(MIN(C3:H3),C3:H3,0) Returns the relative position of the price given the range C3:H3

MATCH(600,C3:H3,0)=1

►INDEX($C$2:$H$2, 1) returns the customer name given the column number by MATCH

**NB: **This formula does not return multiple duplicates as shown below in the case of Tables. There are 2 suppliers with the same minimum price yet it only returns the first one “Tracy Blumstein”

### How do we return multiple items in a Horizontal Lookup?

You need to use a combination of INDEX, SMALL & IF array formula.

{=IFERROR(INDEX($C$2:$H$2,0,SMALL(IF($C3:$H3=MIN($C3:$H3),COLUMN($C3:$H3)-COLUMN($C3)+1),COLUMN(A$1))),"")}

**NB: To understand the array formula, see the explanation on this article OR watch this Video**

# HLOOKUP

This is my least recommended formula because of its Limitations, the biggest being **Look up value must be in the top row of a table.**

=HLOOKUP(MIN(C3:H3),C3:$H$20,ROW($C$20:$H$20)-(ROW(A3)-ROW($A$1)),FALSE)

## How It Works;

►MIN(C3:H3) Returns the minimum price for the item in row 3

MIN(C3:H3)=600

►C3:$H$20 Returns a dynamic **table_array **that ensures that prices per item are at the top row.

►ROW($C$20:$H$20)-(ROW(A3)-ROW($A$1)) Ensures the added customers names row at the end of the table is returned

**NB:** This formula does not return Multiple items, So it is wrong in the case of “Tables” above

No idea how to make HLOOKUP return multiple rows. If you know, Please share in the comments below.

Maybe VBA is the most effective, but it’s the most inefficient (try to use that function 10k times in a sheet).

Besides, K factor in vba function is not robust (as in hlook function): if you insert a row between the Key row and the row you want as result, you have to change K to K+1…

If it’s possible, using native excel functions is always the best solution, so I recomend to use index+match (and if you insert a row in the table, the function still works changing anything).

Thanks Edo for the comment. I have tried to add rows incase of VBA and it still works. The trick is to ensure your K is always current row less header row.

Usually a small to medium-sized bet in relation to the

jackpot sizing may show they overlooked.

Hi Crispo

If you are thinking of an update here are another couple of formulas for your collection.

Single value returned

= LOOKUP( 1, 1 / ( ItemPrice=MIN(ItemPrice) ), Supplier )

Concatenated list returned

= TEXTJOIN( “,”, 1, bestPriceSupplier)

where the named formula ‘bestPriceSupplier’ refers to

= IF(ItemPrice=MIN(ItemPrice), Supplier, “” )

Thanks, Peter for the addition.

Will update the article

I love tables, so here is a table solution (no HLOOKUP(), no MATCH(), no VBA)

{=TEXTJOIN(“,”,TRUE,

IF(MIN(tblItems[@[Ken Black]:[Matt Abelman]])=tblItems[@[Ken Black]:[Matt Abelman]],

tblItems[[#Headers],[Ken Black]:[Matt Abelman]],””))}

Thanks Craig….Will give tables a try also