CODEWITHSUNDEEP
excelvba
Ray Bond
Ray Bond

Reputation: 487

Find value in row by column names

I want to find a given ID in the ID column, then return the Date value from the same row.

This needs to be done using header row names since users will add and remove columns.

A example spreadsheet

enter image description here

The headers will always be in row 1, but the number of header columns will vary.

Upvotes: 0

Views: 1985

Answers (3)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

Convert your data to a table. Your data right now is kind of like this:

enter image description here

To quickly create a table in Excel, do the following:

  1. Select the cell or the range in the data.
  2. Select Home > Format as Table.
  3. Pick a table style.
  4. In the Format as Table dialog box, select the checkbox next to My table as headers if you want the first row of the range to be the header row, and then click OK

More info about tables

The trick about using tables is that even if you add/remove columns, the column ID will be associated to a specific name. The name of each column range is the header itself.

So after doing this, to obtain the date associated to given ID, you can use this formula in a cell (out of the table). I got this:

enter image description here

The formula I've used to get the date of given ID is: =INDEX(Table1[Date];MATCH(I2;Table1[ID];0))

The good thing about using tables is that even if you add or remove columns, the formula will work, and even if you change the name of the header, it will work!

enter image description here

As you can see in the image above, the formula works perfectly, even changing everything.

NOTE: The formula will work as long as it finds an ID. If it can't find an ID, it will return an error. Also, it will find the first coincidence, so if the given ID it's a duplicate, it will return the date of the first coincidence.

Upvotes: 0

Rishi Mehta
Rishi Mehta

Reputation: 417

Assuming you need to lookup the column named "ID" and return the value from column "Status", you may use the following formula

=OFFSET(INDIRECT(ADDRESS(1,MATCH("ID",A1:E1,0))),MATCH(K1,OFFSET(INDIRECT(ADDRESS(1,MATCH("ID",A1:E1,0))),1,0,100,1),0),MATCH("Status",A1:E1,0)-MATCH("ID",A1:E1,0),1,1)

Here the value to be looked up is in cell K1

enter image description here

You would see that if status column is moved to say column D in above screenshot, the Status value returned in K2 is different

enter image description here

Just for fun, I changed the name of column A to Status and the formula now returned the value from that column

enter image description here

So it seems to be pretty generic :)

Upvotes: 1

Error 1004
Error 1004

Reputation: 8230

Let s assume that:

  • Headers appears in the first row

Code:

Option Explicit

Sub test()

    Dim LastColumn As Long, LastRow As Long
    Dim IDColumnNo As Range, DateColumnNo As Range, IDposition As Range
    Dim strID As String
    Dim dtDate As Date

    'Set the ID you want to search for. Change to fullfil you needs
    strID = "w"

    With ThisWorkbook.Worksheets("Sheet1")

        'Find the last row of the first column
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'Find the last column of the first row
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

        'Find which is the column with the IDs searching for the Header "ID"
        Set IDColumnNo = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Find("ID", LookIn:=xlValues, Lookat:=xlWhole)

        'Find which is the column with the Dates searching for the Header "Date"
        Set DateColumnNo = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Find("Date", LookIn:=xlValues, Lookat:=xlWhole)

        If Not IDColumnNo Is Nothing And Not DateColumnNo Is Nothing Then

            'Find which the Id we want"
            Set IDposition = .Range(.Cells(2, IDColumnNo.Column), .Cells(LastRow, IDColumnNo.Column)).Find(strID, LookIn:=xlValues, Lookat:=xlWhole)

            If Not IDposition Is Nothing Then
                dtDate = .Cells(IDposition.Row, DateColumnNo.Column).Value
            Else
                MsgBox "ID is missing!"
            End If

        Else

            MsgBox "ID, Date or both headers are missing!"

        End If

    End With

End Sub

Upvotes: 3

Related Questions