-2

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
Rio05UK
  • 1
  • 3
  • Please **edit your question** to show the code you have tried? Also clarify what you mean by ***where it occurs first***. First Row? First Column? Why VBA? Why not worksheet functions? – Ron Rosenfeld Jan 03 '22 at 01:25

1 Answers1

1

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
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Apologies Ron, I am new to VBA programming and found this issue I am having really hard, I was constantly having errors trying to do loops etc with the large volume data. I gave up in the end. the issue i am having with formulas is it constantly crashes the file there is up to 1million rows with 50 columns across. Can i make the above suggestion into a Subroutine? so it all updates in a separate worksheet? – Rio05UK Jan 03 '22 at 08:23
  • @Rio05UK Yes you can make it into a Subroutine. You will need to post your efforts as a new question since this one has been closed. Or, with that much data, you should check to see if Power Query is more efficient. – Ron Rosenfeld Jan 03 '22 at 12:48