excel vba assistance in lookup column and row

CalmSoul used Ask the Experts™
Lets us assume we have following spreadsheet columns A to F and 4 rows

I would like to write a excel VBA code or some kind of lookup function to find all occurrence of "1" and give products from column "F" and Stores from "Row 1" as sample provided below.

[A]             [B]            [C]           [D]             [E]            [F]
Store 1     Store 2     Store 3     Store 4     Store 5 
                                                                  1               Coffee
                      1           1                1           1               Tea
1                                                   1                              Green Tea

Open in new window


Coffee|Store 5
Green Tea|Store1,Store4
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

In G2, enter this formula, then copy down as needed:


If you want spaces:

=MID(IF(A2=1,", "&A$1,"")&IF(B2=1,", "&B$1,"")&IF(C2=1,", "&C$1,"")&IF(D2=1,", "&D$1,"")&IF(E2=1,", "&E$1,""),3,1000)
Put this function in a module

Function Stores(Item As Range, StoreNames As Range, StoreUsed As Range) As String
    Dim i As Integer
    Stores = ""
    For i = 1 To StoreNames.Columns.Count
        If StoreUsed.Cells(1, i) = 1 Then
            If Len(Stores) = 0 Then
                Stores = "|" + StoreNames.Cells(1, i)
                Stores = Stores + "," + StoreNames.Cells(1, i)
            End If
        End If
    Next i
    If Len(Stores) > 0 Then
        Stores = Item.Value + Stores
    End If
End Function

Open in new window

Then the formula for Coffee is

Open in new window

First part = the cell with the item name
Second = range with the store names
Last = range with the numbers

See file


hi hgholt:

Thanks for fantastic solution ..

Can I make a list of items instead of "," for stores?


Green Tea
Store 1
Store 3
Store 4
That is possible, but not with something that works like a lookup function.

The function is a UDF (User Defined Function) and is like any formula in a cell, returning the result in the cell.
To return the result over several cells, making a list, makes the result unpredictable, as it will overwrite other cells.

Just for a demo, is a macro that will place the result on the sheet ResultList.
Using some named ranges on the sheet as input.
Press the button to run the macro.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial