CODEWITHSUNDEEP
excelvbaloopsmultidimensional-arrayreverse-lookup
Rio05UK
Rio05UK

Reputation: 1

Reverse Lookup 2D array in VBA to get Column name from list of data

I am new to VBA programming and struggling to get my head around this issue I am having.

I need help using Excel VBA for large volume 2D matrix data, how to get the Column name from the data table based on a specific set of lookup values from another sheet, a reverse lookup using VBA code?

The look up value can be duplicated but we want to capture its column name where it occurs first in the column, see below example.

The reason for using VBA is due to the large volume of data and excel is the only appropriate tool.

any ideas how?

Lookup Value Col Name
A101 L2
A102 L3
A201 L2
A304 L5

2D Table

ref L1 L2 L3 L4 L5
Cx1 A100 A101 A102 A102 A102
Cx2 A100 A101 A102 A103 A104
Cx3 A111 A111 A113 A114 A114
Cx4 A200 A201 A201 A201 A201
Cx5 A300 A301 A302 A303 A304

Upvotes: -2

Views: 255

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

There's no need for VBA. If you are looking for the first column in which Lookup Value appears, you can use:

=INDEX(Table1[#Headers],AGGREGATE(15,6,1/(J2=Table1)*COLUMN(Table1)-INDEX(COLUMN(Table1)-1,1),1))

Note that I used a Table, but you can use regular addressing if you prefer.

enter image description here

Algorithm

  • create a 2D array of {TRUE,FALSE} based on the equality
  • 1/theArray => array of {1,#DIV/0!}
  • Multiply by the column number; then subtract the first column of the Table (minus 1) to create a number suitable for indexing into the Table
  • Use the AGGREGATE function with the SMALL argument, and the option to ignore errors, to return the smallest index column argument for the lookup value
  • Use INDEX to return the appropriate value from the #Headers array

If you must use VBA, you can create a User Defined Function:

Option Explicit
Function Header(lookup_value, table As Range) As String
    Dim c As Range
    Set c = table.Find(what:=lookup_value, LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
    If Not c Is Nothing Then Header = table(1, c.Column - table.Column + 1)
End Function

Upvotes: 1

Related Questions